Avatar of 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
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

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

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:


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.
Avatar of kaufmed
Flag of United States of America image

The only way to use regex in SQL Server is to enable CLR integration. Your DBAs may not like that idea, though.
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.
Actually that is not correct either.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of 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.
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
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo