Check string contains numbers in T-SQL

I know this is looking very simple when we read. But, I had a requirement where I need to filter strings from string data type column in T-SQL. Means, some strings in database having numbers in them, and I need to get them out and do some processing. When I started implementing this, I had so many ideas and thought like, looping through all characters in each string and check whether it has numbers in it or not. But, this is not efficient and may be a stupid implementation. And after thought about it sometime, started reading T-SQL documentation and found a wonderful function which is already built in in T-sql. I am saved. Very happy to see that function and that is working very great.

The function name called "PATINDEX". Which requires two parameters. 1. Regular expression and another is 2. input string. It returns the value of the index where it finds the given expression. If it is greater than 0 then it found the match, otherwise no.

Usage:

DECLARE @inputStr Varchar(100);

SET @inputStr = 'This costs 35$';

IF PATINDEX('%[0-9]%',@inputStr) > 0

BEGIN

--Write some logic here

PRINT 'YES, The string has numbers';

END

ELSE

BEGIN

--Write some logic here.

PRINT 'NO, The string does not have numbers';

END

It's very simple. Right? Now, I can use this in the single SELECT query and using CASE, WHEN I can display all strings which has the numbers in it from a table.

And now, vice versa.. How to find whether a string contains alphabets. [There are possibilities that in database may have bad data. The numbers are also stored in the string type. A very bad design, but when we got to working on these type of projects, we have to find a way to refine it and make it more reliable and efficient.] So, in the same code, just change the first parameter of PATINDEX from '%[0-9]%' to '%[a-z]%'. That's it. The expression change is what we need to do there. You can try with any expression as per your requirements and solve the problems.

Hope you like this post and still we need to know plenty of options already exists in technologies. Explore more and know more and save time.