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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top