1

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:

Number
0123 427 6465
0123 1451
01 23 46 47
0123 945675
07984 473456
0845 46 47
(012377) 5258
0800 586931
012 3668 6098
0 1238592371

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?

mb.32
  • 11
  • 2
  • 1
    `select left(Number,4) from ......` or `select substring(Number,4) from ......` (see [LEFT](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_left) or [SUBSTRING](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring)) – Luuk Sep 03 '22 at 15:35
  • Please share more info ... ("the records are not uniform") – Luuk Sep 03 '22 at 15:42
  • 1
    You could [TRIM](https://www.mysqltutorial.org/mysql-trim/) any leading '(', [REPLACE](https://www.mysqltutorial.org/mysql-string-replace-function.aspx) the spaces with nothing, and check the [LEFT](https://www.mysqltutorial.org/mysql-left-function/) four characters. – Andrew Morton Sep 03 '22 at 15:49
  • SQL isn't good at this sort of thing , maybe use a language which is good with arrays eg python – P.Salmon Sep 03 '22 at 16:01
  • go for a regular expression everyone scares lol – jmvcollaborator Sep 03 '22 at 16:24
  • below theres a query using regex :) – jmvcollaborator Sep 03 '22 at 17:01

3 Answers3

1

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:

Number
0123 427 6465
0123 1451
01 23 46 47
0123 945675
07984 473456
(012377) 5258
012 3668 6098
0 1238592371

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.

griv
  • 2,098
  • 2
  • 12
  • 15
0

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?

Ananth MK
  • 392
  • 1
  • 5
0

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

jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17