jijijijiji jijijijiji - 2 months ago 7
SQL Question

Mysql query using regular expression in mysql

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]';'BK-M47B-38'


I want product that is:
start with 'BK-' followed by any character other
than 'R', and ends with a '-' followed by any two numerals
.
Above is
ms-sql
query and I want myssql version of this query
productNumber is like below:

'BK-M47B-40'
'BK-M82B-44'
'FR-R38B-60'
'ST-9828'

Answer

The equivalent regular expression is pretty similar:

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product 
WHERE ProductNumber REGEXP '^BK[-][^R].*[-][0-9]{2}$'

Key differences:

  • ^ and $ mark the beginning and end of string (otherwise, regular expressions match anywhere in the string.
  • % --> .*
  • The {2} is optional but more colloquial in regular expressions for repeating patterns. Note: you could also use [:digit], but that's longer to type.
  • I forget whether a hyphen is a special character or not, so I just put it in square brackets.