Determine if zip code contains numbers only

Question

I have a field called zip, type char(5), which contains zip codes like

12345
54321
ABCDE

I'd like to check with an sql statement if a zip code contains numbers only.

The following isn't working

SELECT * FROM S1234.PERSON
WHERE ZIP NOT LIKE '%'

It can't work because even '12345' is an "array" of characters (it is '%', right?

I found out that the following is working:

SELECT * FROM S1234.PERSON
WHERE ZIP NOT LIKE ' %'

It has a space before %. Why is this working?

Answer 1

If you use SQL Server 2012 or up the following script should work.

DECLARE @t TABLE (Zip VARCHAR(10))

INSERT INTO @t VALUES ('12345')
INSERT INTO @t VALUES ('54321')
INSERT INTO @t VALUES ('ABCDE')


SELECT *
FROM   @t AS t
WHERE  TRY_CAST(Zip AS NUMERIC) IS NOT NULL

Answer 2

Using answer from here to check if all are digit

SELECT col1,col2
FROM
   (
     SELECT col1,col2,
                 CASE
                     WHEN LENGTH(RTRIM(TRANSLATE(ZIP , '*', ' 0123456789'))) = 0 
                     THEN 0 ELSE 1
                 END as IsAllDigit
     FROM S1234.PERSON
   ) AS Z
WHERE IsAllDigit=0

DB2 doesnot have regular expression facility like MySQL REGEXP

Answer 3

USE ISNUMERIC function; ISUMERIC returns 1 if the parameter contains only numbers and zero if it not

EXAMPLE: SELECT * FROM S1234.PERSON WHERE ISNUMERIC(ZIP) = 1

Your statement doesn't validate against numbers but it says get everything that doesn't start with a space.

Answer 4

Let's suppose you ZIP code is a USA zip code, composed by 5 numbers.

db2 "with val as (
 select *
 from S1234.PERSON t
 where xmlcast(xmlquery('fn:matches(\$ZIP,''^\d{5}$'')') as integer) = 1
)
select * from val"

For more information about xQuery:fn:matches: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/xqrfnmat.html

Answer 5

mySql does not have a native isNumberic() function. This would be pretty straight-forward in Excel with the ISNUMBER() function, or in T-SQL with ISNUMERIC(), but neither work in MySQL so after a little searching around I came across this solution...

SELECT * FROM S1234.PERSON 
WHERE ZIP REGEXP ('[0-9]')

Effectively we're processing a regular expression on the contents of the 'ZIP' field, it may seem like using a sledgehammer to crack a nut and I've no idea how performance would differ from a more simple approach but it worked and I guess that's the point.

Answer 6

I have made more error-prone version based on the solution https://stackoverflow.com/a/36211270/565525, added intermedia result, some examples:

select 
      test_str
    ,                  TRIM(TRANSLATE(replace(trim(test_str), ' ', 'x'), 'yyyyyyyyyyy', '0123456789'))
    , case when length(TRIM(TRANSLATE(replace(trim(test_str), ' ', 'x'), 'yyyyyyyyyyy', '0123456789')))=5 then '5-digit-zip'  else 'not 5d-zip' end is_zip
from (VALUES 
        ('  123  '  )
        ,('  abc  ' )
        ,('  a12  ' )
        ,('  12 3  ')
        ,('  99435 ')
        ,('99323'   )
    ) AS X(test_str)
;

The result for this example set is:

TEST_STR 2        IS_ZIP
-------- -------- -----------
  123    yyy      not 5d-zip
  abc    abc      not 5d-zip
  a12    ayy      not 5d-zip
  12 3   yyxy     not 5d-zip
  99435  yyyyy    5-digit-zip
99323    yyyyy    5-digit-zip

Answer 7

Try checking if there's a difference between lower case and upper case. Numerics and special chars will look the same:

SELECT *
FROM S1234.PERSON 
WHERE UPPER(ZIP COLLATE Latin1_General_CS_AI ) = LOWER(ZIP COLLATE Latin1_General_CS_AI)

Answer 8

Here's a working example for the case where you'd want to check zip codes in a range. You could use this code for inspiration to make a simple single post code check, if you want:

if local_test_environment?
  # SQLite supports GLOB which is similar to LIKE (which it only has limited support for), for matching in strings.
  where("(zip_code NOT GLOB '*[^0-9]*' AND zip_code <> '') AND (CAST(zip_code AS int) >= :range_start AND CAST(zip_code AS int) <= :range_finish)", range_start: range_start, range_finish: range_finish)
else
  # SQLServer supports LIKE with more advanced matching in strings than what SQLite supports.
  # SQLServer supports TRY_PARSE which is non-standard SQL, but fixes the error SQLServer gives with CAST, namely: Conversion failed when converting the nvarchar value 'US-19803' to data type int.
  where("(zip_code NOT LIKE '%[^0-9]%' AND zip_code <> '') AND (TRY_PARSE(zip_code AS int) >= :range_start AND TRY_PARSE(zip_code AS int) <= :range_finish)", range_start: range_start, range_finish: range_finish)
end

Answer 9

Use regex.

SELECT * FROM S1234.PERSON
WHERE ZIP REGEXP '\d+'