Query zip codes with extra digits

Question

Hi,

If my database stores zipcode without being separated by a '-' after the first 5 zip code, how can i pull a query for zip code in 300xx-23xx?

Percentage symbol doesn't work in this case :(

Answer 1

This depends on the used SQL Server version. When you're using SQL Server 2012 it's pretty simple. Use the new TRY_CAST() function:

DECLARE @Sample TABLE ( ZipCode NVARCHAR(255) );

INSERT  INTO @Sample
VALUES  ( 'a' ),
        ( '1000' ),
        ( '2300' ),
        ( '2500' ),
        ( '3000' );

SELECT  *
FROM    @Sample
WHERE   TRY_CAST(ZipCode AS INT) BETWEEN 2300 AND 3000;

Otherwise you can use ISNUMERIC:

SELECT  *
FROM    @Sample
WHERE   ISNUMERIC(ZipCode) = 1
        AND CAST(CAST(ZipCode AS VARCHAR(255)) AS INT) BETWEEN 2300 AND 3000;

The CAST(ZipCode AS VARCHAR(255)) is only necessary when the ZipCode field is NVARCHAR.

Answer 2

Hi Sammeh00,

If the format of the zipcode is fixed, you can fomart the zip code using T-SQL like below:

Select SUBSTRING(zipcode,1,5)+'-'+ SUBSTRING(zipcode,6,4) From TableName

Please feel free to let me know if you have any question.

Thanks,
Eileen

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 3

When you want a match on "300xx-23xx" then use:

DECLARE @Sample TABLE ( ZipCode NVARCHAR(255) );

INSERT  INTO @Sample
VALUES  ( '1234512345' ),
        ( '300002300' );

SELECT  *
FROM    @Sample
WHERE   ZipCode LIKE '300__23__';

Answer 4

Thank you ! I will try both suggestions !