Finding number position in string

Question

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?

@xdzz : With help of your answer, I got my answer. See my answer... — Fahim Parkar, Jun 22 '12 at 11:48
Um, I see. But what about text do not contain number? you will get 999. — xdazz, Jun 22 '12 at 12:20
@xdazz : yes in that case it will give me 999. See [here](http://sqlfiddle.com/#!2/db80a/1) — Fahim Parkar, Jun 22 '12 at 12:25

score 8 · Accepted Answer · answered Jun 22 '12 at 11:48

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

Why not directly do it in your application as you commented on my answer? This query is not.. elegant.. — geertjanvdk, Jun 27 '12 at 10:31

score 6 · Answer 2 · answered Jun 21 '12 at 11:39

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.

@Fahim That was not part of the requirement in the asked question. — geertjanvdk, Jun 21 '12 at 11:48

score 3 · Answer 3 · answered Jun 21 '12 at 09:48

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;

answered Jun 21 '12 at 09:48

xdazz

158,678
38
247
274

+1 MySQL is missing any equivalent to [`PATINDEX()`](http://msdn.microsoft.com/en-us/library/ms188395.aspx), so this is as close as you will get without resorting to regex (which will be even slower, I guess) – Tomalak Jun 21 '12 at 09:56
2

@xdazz : Your query is giving me output as 0 for all rows... See [here](http://sqlfiddle.com/#!2/67733/4) – Fahim Parkar Jun 22 '12 at 11:06
This does not work since locate returns 0 if there is no match. – Juha Palomäki Mar 06 '14 at 01:56

Finding number position in string

3 Answers3

Demo

Linked

Related