82

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

gotqn
  • 42,737
  • 46
  • 157
  • 243
Manish
  • 6,106
  • 19
  • 64
  • 90

4 Answers4

147

Use this:

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

MSDN - LIKE (Transact-SQL)

Sarfaraaz
  • 488
  • 6
  • 17
cjk
  • 45,739
  • 9
  • 81
  • 112
  • I thought square brackets escaped in MSSQL, such as `LIKE '[_]'`? Also out of interest where did you find this information? – Paul C Jan 08 '13 at 10:00
  • 1
    No this does work, I've been doing it for years and have no idea where I first saw it... – cjk Jan 08 '13 at 13:25
  • 1
    @CodeBlend square brackets are used for escaping when *outside* a string, and for delimiting patterns when *inside* a string. – Ian Kemp Feb 26 '14 at 13:32
  • 2
    The square brackets in a LIKE statement are explained here. https://msdn.microsoft.com/en-us/library/ms179859.aspx?f=255&MSPPError=-2147217396 – NTDLS Oct 22 '15 at 18:23
  • Note that you must use [0-9] as shown and not the Regex shorthand [\d] for digit. Worked for me - up vote. – Yogi Feb 23 '18 at 17:28
12
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'
kevchadders
  • 8,335
  • 4
  • 42
  • 61
1

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
eksortso
  • 1,273
  • 3
  • 12
  • 21
0
  1. You could use CLR based UDFs or do a CONTAINS query using all the digits on the search column.
TrustyCoder
  • 4,749
  • 10
  • 66
  • 119