Ask the Expert

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)


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: