Menu
What is the SQL Server equivalent of the MySQL expression below?
... WHERE somefield REGEXP '^[[:blank:]]*ASD[[:blank:]]*$|^[[:blank:]]*ASD[[:blank:]]*[[.vertical-line.]]|[[.vertical-line.]][[:blank:]]*ASD[[:blank:]]*$|[[.vertical-line.]][[:blank:]]*ASD[[:blank:]]*[[.vertical-line.]]'
624
MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator. It provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems. REGEXP is the operator used when performing regular expression pattern matches.
MySQL REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns 1, else it returns 0. If either expression or pattern is NULL, the function returns NULL.
You can use RegEx in many languages like PHP, Python, and also SQL. RegEx lets you match patterns by character class (like all letters, or just vowels, or all digits), between alternatives, and other really flexible options.
A Regular Expression is popularly known as RegEx, is a generalized expression that is used to match patterns with various sequences of characters. A RegEx can be a combination of different data types such as integer, special characters, Strings, images, etc.
Unfortunately the regex support in mssql is dreadful, the closest operator is "like" which misses out on the functionality of regex's by a mile. You would have to look at breaking the regex up into multiple like statements and probably doing some dirty string manipulation to emulate what you are attempting to achieve.
For example while we could replicate the [[:blank:]] with [ ] (read [ Space Tab ]) we cant force matching zero or more of them, so instead we have to strip them out of the expression but this would match ' A S D ' so we need to test for the presence of ASD in the unmodified string.
I think the following would replace your regex but it was thrown together quickly so test it carefully.
replace(replace(somefield,' ',''),' ','') in ('ASD','|ASD','|ASD|','ASD|')
and
somefield like '%ASD%'
Again in my replace statements one is a space the other a tab.
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
