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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your toughest questions.
This was first published in April 2001