Mysql query using regular expression in mysql

Question

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 mysql version of this query productNumber is like below :

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

Gordon Linoff · Accepted Answer · 2015-11-07 12:59:41Z

1

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.

answered Nov 7, 2015 at 12:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2015-11-07 12:59:39Z

1

Use RLIKE:

SELECT *
FROM Product 
WHERE ProductNumber RLIKE '^BK-[^R].*-[0-9][0-9]$';

SqlFiddleDemo

edited Nov 7, 2015 at 12:59

answered Nov 7, 2015 at 12:54

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Mysql query using regular expression in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related