5

Below is what I have in table myTable

+++++++++++++++ + id + myWord + +++++++++++++++ + 1 + AB123 + + 2 + A413D + + 3 + X5231 + + 4 + ABE921 + +++++++++++++++

When I execute 

SELECT id, Locate('1',myWord) as myPos FROM myTable;

I get position of 1.

+++++++++++++++ + id + myPos + +++++++++++++++ + 1 + 3 + + 2 + 3 + + 3 + 5 + + 4 + 6 + +++++++++++++++

What I want to achieve is finding first position of integer so that I will have below output.

+++++++++++++++++++++++ + id + myWord + myPos + +++++++++++++++++++++++ + 1 + AB123 + 3 + + 2 + A413D + 2 + + 3 + X5231 + 2 + + 4 + ABE921 + 4 + +++++++++++++++++++++++

Any Idea how I can achieve this?

Improve this question
5
  • @xdazz : your answer is giving me output as 0 for all rows. Commented Jun 22, 2012 at 11:30
  • @xdzz : With help of your answer, I got my answer. See my answer... Commented Jun 22, 2012 at 11:48
  • Um, I see. But what about text do not contain number? you will get 999. Commented Jun 22, 2012 at 12:20
  • @xdazz : yes in that case it will give me 999. See here Commented Jun 22, 2012 at 12:25
  • @xdazz : but in my database, there is a number in string. Commented Jun 22, 2012 at 12:26

3 Answers 3

Reset to default
8

With help of xdazz answer, I did some changes and got answer finally...

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;

Demo

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

1 Comment

Why not directly do it in your application as you commented on my answer? This query is not.. elegant..
6

If you do this often with MySQL, you better make a Stored Function using the string functions SUBSTRING() and ASCII().

DELIMITER //; CREATE FUNCTION find_first_int(pData CHAR(10)) RETURNS INT BEGIN DECLARE vPos INT DEFAULT 1; DECLARE vRes INT DEFAULT 0; DECLARE vChar INT; WHILE vPos <= LENGTH(pData) DO SET vChar = ASCII(SUBSTR(pData, vPos, 1)); IF vChar BETWEEN 48 AND 57 THEN RETURN vPos; END IF; SET vPos = vPos + 1; END WHILE; RETURN NULL; END// DELIMITER ;//

The result:

mysql> SELECT id, myWord, find_first_int(myWord) AS myPos FROM t1; +------+--------+-------+ | id | myWord | myPos | +------+--------+-------+ | 1 | AB123 | 3 | | 2 | A413D | 2 | | 3 | X5231 | 2 | | 4 | ABE921 | 4 | | 5 | ABC | NULL | +------+--------+-------+

The NULL result can be changed using the function IFNULL().

Note that the function only accepts CHAR(10), so you might want to change that for longer data.

Improve this answer

1 Comment

@Fahim That was not part of the requirement in the asked question.
3

Not smart, but I think you could do this:

SELECT id, LEAST( Locate('0',myWord), Locate('1',myWord), Locate('2',myWord), Locate('3',myWord), Locate('4',myWord), Locate('5',myWord), Locate('6',myWord), Locate('7',myWord), Locate('8',myWord), Locate('9',myWord) ) as myPos FROM myTable;
Improve this answer

3 Comments

+1 MySQL is missing any equivalent to PATINDEX(), so this is as close as you will get without resorting to regex (which will be even slower, I guess)
@xdazz : Your query is giving me output as 0 for all rows... See here
This does not work since locate returns 0 if there is no match.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.