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.
SQLUser212Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
Hi, I think you should escape the dot as you mean it literal, so \. instead of . in your pattern. Can you try that?
0
Terry WoodsIT GuruCommented:
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.
0
käµfm³d 👽Commented:
The only way to use regex in SQL Server is to enable CLR integration. Your DBAs may not like that idea, though.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Anthony PerkinsCommented:
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.
0
Anthony PerkinsCommented:
Actually that is not correct either.
0
Anthony PerkinsCommented:
This seems right:
PARSENAME(ID , 2) LIKE '[a-z]%' AND PARSENAME(ID , 1) NOT LIKE  '%[^0-9]%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLUser212Author Commented:
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.
0
Anthony PerkinsCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.