3

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

2 Answers 2

1

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.

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

Comments

0

See this User defined functions.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.