Q

Line returning no errors nor records when it should

This line returns no errors and returns no records when I am certain that it should. Have I missed something?

SELECT ListID, lastName, postalCode, telephone FROM dbo.A WHERE (lastName ='Zed') AND (postalCode = 'V7C 1C6') AND (telephone = RIGHT(3238, 4)) AND (ListID = 3)


Everything looks okay except the RIGHT function.

If the telephone column has a numeric datatype, and if the telephone numbers are always exactly 4 digits, then you can simply use

... AND telephone = 3238

If the telephone column has a numeric datatype, but the telephone numbers are more than 4 digits, then you should be performing the RIGHT function on the telephone number, not on the literal 3238 that you're testing against. Furthermore, the RIGHT function operates on a character string, so you should convert the telephone number, because it's always safer to use an explicit conversion than to leave it up to an implicit conversion by the database (in some cases, and I think this is one of them, the implicit conversion from numeric to character is not done). Finally, I like to ensure there are no trailing blanks in by always using RTRIM to trim blanks from the right --

... AND RIGHT(RTRIM(CAST(telephone as VARCHAR(16))),4)='3238'

Notice that now that we're dealing with strings, you have to put 3238 in quotes.

If the telephone number is actually a character datatype, you should still convert it to VARCHAR and strip trailing blanks as above, to guard against short numbers left-justified in the fixed character field. Just choose a VARCHAR size big enough to hold the field.

For More Information


This was first published in April 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close