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.
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.