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.122
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.
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.
Last Comment
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?
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
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
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 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.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
