SQL regular expression to check if a string contains a certain substring

Question

I am using SQL and need to check if a string contains a certain substring using regular expressions. Lower and upper case letters and space are allowed.

Currently, I have Where description = '/^[a-zA-Z\s]mysubstring[a-zA-Z\s]$/'

But it is not working for some reason. Any idea?

Solution

MySQL regex is not supporting the same syntax as PHP PCRE regex. If you want to use a regex check, you must use the REGEXP operator. Next, it does not support \s and other shorthand classes, you need to replace it with a [:space:] POSIX character class.

Also, MySQL REGEXP will perform a case insensitive regex check by default, but you still can use [A-Za-z] just in case you have non-standard options.

Use

WHERE description REGEXP '^[a-zA-Z[:space:]]*mysubstring[a-zA-Z[:space:]]*$'

If you do not care about what there is in the entries and you just need to find those containing your string, use LIKE with % (=any text) wildcard:

WHERE description LIKE '%mysubstring%'