Avatar of SQLUser212
SQLUser212 asked on

Regular expression in SQL select/update statement

Trying to write like regular expression in SQL select/update staements to get the values that look like any of these [“H6P.5“,“Z3C.10“,“D1V.1222“] where
1.       the first character is an upper or lower case letter
2.       followed by anything in between then a period
3.       followed by matching the any set of repeating numbers, i.e. 12+ matches 122, 1222, and so on.

I tried this
select * from table1 WHERE ID LIKE  '[A-Za-z]%.[0-9]%', but it also gets the values like H6P.5H, D1V.1JJ

I also tried this LIKE '[A-Za-z]%.[0-9]#', '[A-Za-z]%.[0-9]+', '[A-Za-z]%.[0-9]@', '[A-Za-z]%.[0-9]*' but won't get any values.

Please suggest.
Microsoft SQL ServerRegular Expressions

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Gerwin Jansen

Hi, I think you should escape the dot as you mean it literal, so \. instead of . in your pattern. Can you try that?
Terry Woods

The LIKE operator doesn't accept a regex pattern, so you can't use * to look for a repeat of the previous character. Details of what you can do with it are here:

http://technet.microsoft.com/en-us/library/ms179859.aspx

The . character has no special meaning, so doesn't need escaping. LIKE doesn't allow for any way of matching an unknown number of digits.
kaufmed

The only way to use regex in SQL Server is to enable CLR integration. Your DBAs may not like that idea, though.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Anthony Perkins

This would do it, just don't expect stellar performance:
PARSENAME(ID , 2) LIKE '[a-z]%' AND PARSENAME(ID , 1) NOT LIKE  '[^0-9]'

For full Regex expressions use some CLR function.
Anthony Perkins

Actually that is not correct either.
ASKER CERTIFIED SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
SQLUser212

Thanks! This works for me to check Alphabets after period (.), but i also need to check the 3rd test case for matching the any set of repeating numbers, i.e. 12+ matches 122, 1222, and so on.

Please suggest.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

but i also need to check the 3rd test case for matching the any set of repeating numbers
For that you will need true RegEx and if you need this with T-SQL than you will have to use CLR for that.  If you search for this on Google you will find hundreds of examples.  The following is a good article on the subject:
CLR Assembly RegEx Functions for SQL Server by Example