KuljeetSingh.com

Feb 15, 2013

How To Check If String Contains Numeric Number (SQL Server)

Yesterday I was working on a project where we need to exclude those records where column contains numeric number in value even if its a single numeric digit. So I googled a bit and found a very easy trick.... Using simple regular expression
Using simple regular expression method we can check in both Query and condition as well

In First Scenario we will query the records which contains atleast one numeric digit
SELECT * FROM Table WHERE Column LIKE '%[0-9]%'
Or
SELECT * FROM Table WHERE PATINDEX('%[0-9]%',Column) > 0
This query will extract records where column have value containing atleast one numeric digit.

We can also extract the records where column do not have any numeric digit using NOT LIKE instead of LIKE
SELECT * FROM Table WHERE Column NOT LIKE '%[0-9]%'
Or
SELECT * FROM Table WHERE PATINDEX('%[0-9]%',Column) = 0

Both these methods can also be used to check the validation in variables also
DECLARE @str AS VARCHAR(50)
SET @str = '1 STRING CONTAINS 2 ATLEAST 3 NUMBERS'

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

Or

DECLARE @str AS VARCHAR(50)
SET @str = '1 STRING CONTAINS 2 ATLEAST 3 NUMBERS'

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

Hope, it may be useful.

 
?

Blogs | About Me | My Projects | Contact Me
【丰胸】虽然,这些药物对于乳房而言粉嫩公主,能够起到一定的丰胸效果,可朋友们这些药物的后遗症,也会在我们服完后慢慢显现出来丰胸产品,譬如:机体中的雌激素水平被打破了,就容易导致我们的月经变得不正常丰胸食物,从而使得我们的腰跟着变得圆嘟嘟,而严重的话还可能会导致我们的子宫出血,肝脏受损丰胸效果,所以说若是采用药物来进行丰胸,大家还是小心谨慎些为好。