CODEWITHSUNDEEP
mysqlregex
Fahim Parkar
Fahim Parkar

Reputation: 31627

use of regex in mysql

I have query as shown below.

SELECT 
  myWord, 
  LEAST (
    if (Locate('0',myWord) >0,Locate('0',myWord),999),
    if (Locate('1',myWord) >0,Locate('1',myWord),999),
    if (Locate('2',myWord) >0,Locate('2',myWord),999),
    if (Locate('3',myWord) >0,Locate('3',myWord),999),
    if (Locate('4',myWord) >0,Locate('4',myWord),999),
    if (Locate('5',myWord) >0,Locate('5',myWord),999),
    if (Locate('6',myWord) >0,Locate('6',myWord),999),
    if (Locate('7',myWord) >0,Locate('7',myWord),999),
    if (Locate('8',myWord) >0,Locate('8',myWord),999),
    if (Locate('9',myWord) >0,Locate('9',myWord),999)
  ) as myPos
FROM myTable;

Using this query, I am finding the position of first integer in a string.

My Question Is

Using regex how can I eliminate those 10 lines that I have as shown below.

    if (Locate('0',myWord) >0,Locate('0',myWord),999),
    if (Locate('1',myWord) >0,Locate('1',myWord),999),
    if (Locate('2',myWord) >0,Locate('2',myWord),999),
    if (Locate('3',myWord) >0,Locate('3',myWord),999),
    if (Locate('4',myWord) >0,Locate('4',myWord),999),
    if (Locate('5',myWord) >0,Locate('5',myWord),999),
    if (Locate('6',myWord) >0,Locate('6',myWord),999),
    if (Locate('7',myWord) >0,Locate('7',myWord),999),
    if (Locate('8',myWord) >0,Locate('8',myWord),999),
    if (Locate('9',myWord) >0,Locate('9',myWord),999)

Any idea how to get this done?

Demo

Upvotes: 3

Views: 2754

Answers (2)

juergen d
juergen d

Reputation: 204746

See this User defined functions.

You could use REGEXP_INSTR? from there to find the position of the numbers. (haven't tested it though.)

Upvotes: 0

simbabque
simbabque

Reputation: 54323

Have a look at MySQL: Use REGEX to extract string (select REGEX) and the first result of google "mysql regex locate". It seems that it is not possible to do that with a regular expression in mysql. Your solution is already a good way to do it.

Upvotes: 1

Related Questions