T SQL wildcard searching for a zip code

Question

I've been messing around all day with some t-sql code for searching a zip code. I'm using a wild card to match the end of the string. However, it is not working. It only works when the whole string is entered correctly.

Here is the code I have.

IF (@MailFiveDigitZip <> '')
            BEGIN
               SET @Conditions = @Conditions + ' AND (cast(MailFiveDigitZip AS Varchar(5)) LIKE cast(@MailFiveDigitZip AS Varchar(5)) + ''%'' )'
        END;

@MailFiveDigitZip is a param and it is a char(5). It is being added to a variable that holds multiple search conditions. I have other code that is similar to this, in fact identical except for the the other code has a zip code that is a nullable int, and so the if conditions is '@zipCode is not null'. However, my other code works and returns values, even for inputs such as "44" and "543", it does what the wild card is suppose to do and finds all the strings beginning with the input value.

Any help or suggestions would be appreciated.

Answer 1

If what you said is true, that @MailFiveDigitZip is a char and not a varchar, then you're running into character padding issues.

"44" in a varchar(5) is "44"

"44" in a char(5) is "44 " (note in case it doesn't translate well... this is 44 plus 3 spaces)

like "44 %" would not meet your query criteria.

To correct this, just update your variable @MailFiveDigitZip from a char(5) to a varchar(5)

Answer 2

Why not use:

AND (
left(MailFiveDigitZip,1)=@MailFiveDigitZip OR
left(MailFiveDigitZip,2)=@MailFiveDigitZip OR
left(MailFiveDigitZip,3)=@MailFiveDigitZip OR
left(MailFiveDigitZip,4)=@MailFiveDigitZip OR
left(MailFiveDigitZip,5)=@MailFiveDigitZip
)

It should work much faster as well..