How to detect if a string contains at least a number?

Question

How to detect if a string contains at least a number (digit) in SQL server 2005?

Solution 1

Use this:

SELECT * FROM Table WHERE Column LIKE '%[0-9]%'

MSDN - LIKE (Transact-SQL)

Solution 2

DECLARE @str AS VARCHAR(50)
SET @str = 'PONIES!!...pon1es!!...p0n1es!!'

IF PATINDEX('%[0-9]%', @str) > 0
   PRINT 'YES, The string has numbers'
ELSE
   PRINT 'NO, The string does not have numbers' 

Solution 3

The simplest method is to use LIKE:

SELECT CASE WHEN 'FDAJLK' LIKE '%[0-9]%' THEN 'True' ELSE 'False' END;  -- False
SELECT CASE WHEN 'FDAJ1K' LIKE '%[0-9]%' THEN 'True' ELSE 'False' END;  -- True

Comments

[ZygD]

Why PATINDEX, rather than a simple LIKE?

[Coops]

I thought square brackets escaped in MSSQL, such as LIKE '[_]'? Also out of interest where did you find this information?

[cjk]

No this does work, I've been doing it for years and have no idea where I first saw it...

[Ian Kemp]

@CodeBlend square brackets are used for escaping when outside a string, and for delimiting patterns when inside a string.

[NTDLS]

The square brackets in a LIKE statement are explained here. msdn.microsoft.com/en-us/library/…

[Yogi]

Note that you must use [0-9] as shown and not the Regex shorthand [\d] for digit. Worked for me - up vote.