I want to run a query in mysql which will return the record where the first 4 digits are '0123' or '0798' from the following column:

I want the query to return all records where '0123' or '0798' are the first 4 numeric characters regardless of if there are other characters before or in between. E.g. I would want record 7 returned even though '0123' is in brackets. And I would want record 10 returned even though it is written as '0 123' i.e. there is a space in between.

Is regex relevant here? If so, what would the regex expression be?

CodePudding user response：

Use a combination of LEFT and REPLACE. REPLACE will strip out any unwanted brackets and whitespaces, and LEFT will select the first four characters, starting from left, of the newly formatted value which will be used in the WHERE clause selecting for values IN '0123', '0798'.

SELECT `number` FROM Numbers WHERE LEFT(REPLACE(REPLACE(REPLACE(`number`, '(', ''), ')', ''), ' ', ''), 4) IN ('0123', '0798')

Fiddle.

Result:

Also, it's worth noting, number is a Reserved Word in MySQL. I used backticks ` to escape it, however, it is advised that you do not use reserved words in your naming conventions.

CodePudding user response：

We can use REGEXP_REPLACE function to remove all others characters other than number and get first four using the below query,

SELECT LEFT(REGEXP_REPLACE(Number, '[^0-9] ', ''), 4) as 4digitonly FROM Numbers a;

Please refer How to get only Digits from String in mysql?

CodePudding user response：

Nothing is better than regex, yes they make us think even think recursivelly :) Here is the query(of course it can be refactored N times):

SELECT n.number FROM Numbers n WHERE n.number REGEXP '^.*(0[ \t\r\n]*1[ \t\r\n]*2[ \t\r\n]*3).*|^.*(0[ \t\r\n]*7[ \t\r\n]*9[ \t\r\n]*8).*$'

Fiddle