CODEWITHSUNDEEP
sqlsql-server
OMG
OMG

Reputation: 263

Case Statement producing Incorrect Result when Multiple check Occur in sql

I am trying to validate ZIP codes in SQL Server 2012.

Conditions: ZIP should start and end with Number and it should be only 5 and 10 digit; if it is 10 digit than after 5th digit - and end with 4 digit

declare @mytab table (zip VARCHAR(50))

INSERT INTO @mytab
    SELECT '54825' UNION ALL
    SELECT '4459' UNION ALL
    SELECT '68523' UNION ALL
    SELECT '57425-2342' UNION ALL
    SELECT 'ABC12' UNION ALL
    SELECT 'ZR2' UNION ALL
    SELECT '79425-2342' UNION ALL
    SELECT '794252342' UNION ALL
    SELECT '794252342ASWS'

--SELECT * FROM @mytab

SELECT 
    ZIP,
    CASE 
       WHEN LEN(ZIP) = 5 THEN 'VALID'
       WHEN SUBSTRING(ZIP, CHARINDEX('[0-9][0-9][0-9][0-9][0-9]', ZIP), 5) = ZIP THEN 'VALID'
       WHEN SUBSTRING(ZIP, CHARINDEX('[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]', ZIP), 10) = ZIP THEN 'VALID'
       ELSE 'INVALID' 
    END AS RES
FROM 
    @mytab

Here, 4459, ABC12, ZR2, 794252342 are invalid but are recognized as valid

and

57425-2342, 79425-2342 are valid but labeled as invalid.

Am I missing anything? Please help.

Upvotes: 1

Views: 73

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

Use LIKE:

SELECT ZIP,
       (CASE WHEN ZIP LIKE '[0-9][0-9][0-9][0-9][0-9]' THEN 'VALID'
             WHEN ZIP LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 'VALID'
             ELSE 'INVALID'
        END) as res
THEN 'VALID'
FROM @mytab;

Upvotes: 1

arce.est
arce.est

Reputation: 379

I used the functions of REVERSE and ISNUMERIC to validate the fields, in the explanation of @Jakub_Ojmucianski, it has an error when validating. (try with 79-4252342)

DECLARE @mytab TABLE (zip VARCHAR(50))

INSERT INTO @mytab
    SELECT '79-4252342' UNION ALL
    SELECT '4459' UNION ALL
    SELECT '68523' UNION ALL
    SELECT '57425-2342' UNION ALL
    SELECT 'ABC12' UNION ALL
    SELECT 'ZR2' UNION ALL
    SELECT '79425-2342' UNION ALL
    SELECT '794252342' UNION ALL
    SELECT '794252342ASWS'

SELECT 
    ZIP,
    CASE 
        WHEN ISNUMERIC(SUBSTRING(ZIP,1,5)) = 1 AND LEN(ZIP) = 5 
            THEN 'VALID'
        WHEN ISNUMERIC(SUBSTRING(ZIP,1,5)) = 1 AND LEN(ZIP) = 10 AND ISNUMERIC(SUBSTRING(REVERSE(ZIP),1,4)) = 1
            THEN 'VALID'
    ELSE 'INVALID'
    END AS RES
FROM 
    @mytab

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

What you're missing is that SUBSTRING doesn't reject invalid index values for start that are less than 1, the first character of the string. That means that

SUBSTRING(ZIP,CHARINDEX('[0-9][0-9][0-9][0-9][0-9]',ZIP),5)

Will return up to the first 41 characters of ZIP even if it doesn't match the pattern (because CHARINDEX returns 0 in that case). Similarly for the 10 character version returning up to 9 characters. (I say "up to" because obviously some inputs don't have 4/9 characters)

Just use something like:

CASE 
       WHEN ZIP LIKE '[0-9][0-9][0-9][0-9][0-9]' THEN 'VALID'
       WHEN ZIP LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'THEN 'VALID'
       ELSE
       'INVALID' END AS RES

1 1 less than length because:

If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length - 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

Upvotes: 4

Related Questions