Selecting first 4 numbers from a string MySQL

Question

If I have strings like this:

CC123484556
CC492014512
BUXT122256690

How can I manipulate code like this in MySQL to pull the first 4 values that are numbers ? There are various # of letters before numbers in other rows but the most important thing are the first 4 numbers that show up.

SELECT LEFT(alloy , 4) FROM tbl

So the desired result would be:

1234
4920
1222

Lukasz Szozda · Answer 1 · 2017-09-28T15:12:01.403

Slow and ugly:

SELECT col,
       SUBSTRING(tab.col, MIN(LOCATE(four_digits, tab.col,1)), 4) + 0 AS result
FROM  (SELECT 'CC123484556' AS col UNION ALL
       SELECT 'CC492014512' UNION ALL
       SELECT 'BUXT122256690' UNION ALL
       SELECT 'abced') tab
CROSS JOIN (
   SELECT  CONCAT(d1.z, d2.z, d3.z, d4.z) AS four_digits
   FROM (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
            SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
            SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d1
  CROSS JOIN (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
            SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
            SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d2
  CROSS JOIN (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
            SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
            SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d3
  CROSS JOIN (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
            SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
            SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d4
 ) sub
WHERE LOCATE(four_digits, tab.col,1) > 0
GROUP BY col;

Rextester Demo

Generate all 4 digit combinations, locate them in string and get substring with lowest index.

EDIT:

A bit faster approach:

SELECT col, SUBSTRING(col, MIN(i), 4) + 0 AS r
FROM (
    SELECT col, SUBSTRING(tab.col, i , 4) + 0 AS result, i
    FROM tab
    CROSS JOIN (
       SELECT  CONCAT(d1.z, d2.z)+1 AS i
       FROM (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
                SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
                SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d1
      CROSS JOIN (SELECT '1' AS z UNION SELECT '2' UNION SELECT '3' UNION
                SELECT '4' UNION SELECT '5' UNION SELECT '6' UNION
                SELECT '7' UNION SELECT '8' UNION SELECT '9' UNION SELECT '0') d2
     ) sub
     WHERE  i <= LENGTH(tab.col)-1
) sub
WHERE result <> 0
GROUP BY col;

Rextester Demo2

Get 4 character substring from beginning, convert implicitly to number, get number with lowest i.

Probably easiest way is to `REPLACE` all non-digit characters with blanks and use `LEFT(..., 4) + 0` on result. Unfortunately MySQL does not support REGEX_REPLACE. — Lukasz Szozda, Sep 28 '17 at 14:53

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Using locate(), least(), and substr()

SQLFiddle Demo

select col,SUBSTR(col,LEAST(
    if (Locate(0,col) >0,Locate(0,col),999),
    if (Locate(1,col) >0,Locate(1,col),999),
    if (Locate(2,col) >0,Locate(2,col),999),
    if (Locate(3,col) >0,Locate(3,col),999),
    if (Locate(4,col) >0,Locate(4,col),999),
    if (Locate(5,col) >0,Locate(5,col),999),
    if (Locate(6,col) >0,Locate(6,col),999),
    if (Locate(7,col) >0,Locate(7,col),999),
    if (Locate(8,col) >0,Locate(8,col),999),
    if (Locate(9,col) >0,Locate(9,col),999)
  ),4) as result from test;

Test Results:

mysql> create table test ( col varchar(15));
Query OK, 0 rows affected (0.70 sec)

mysql> insert into test (col) values 
    -> ('CC123484556'),
    -> ('CC492014512'),
    -> ('BUXT122256690');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

Output:

mysql> select * from test;
+---------------+
| col           |
+---------------+
| CC123484556   |
| CC492014512   |
| BUXT122256690 |
+---------------+
3 rows in set (0.00 sec)

mysql> select col,SUBSTR(col,LEAST(
    ->     if (Locate(0,col) >0,Locate(0,col),999),
    ->     if (Locate(1,col) >0,Locate(1,col),999),
    ->     if (Locate(2,col) >0,Locate(2,col),999),
    ->     if (Locate(3,col) >0,Locate(3,col),999),
    ->     if (Locate(4,col) >0,Locate(4,col),999),
    ->     if (Locate(5,col) >0,Locate(5,col),999),
    ->     if (Locate(6,col) >0,Locate(6,col),999),
    ->     if (Locate(7,col) >0,Locate(7,col),999),
    ->     if (Locate(8,col) >0,Locate(8,col),999),
    ->     if (Locate(9,col) >0,Locate(9,col),999)
    ->   ),4) as result from test;
+---------------+--------+
| col           | result |
+---------------+--------+
| CC123484556   | 1234   |
| CC492014512   | 4920   |
| BUXT122256690 | 1222   |
+---------------+--------+
3 rows in set (0.00 sec)

@lad2025 thank you, sometimes it comes in mind, but it doesn't come when it require :) — Akshay Hegde, Sep 28 '17 at 15:14

Selecting first 4 numbers from a string MySQL

2 Answers2

SQLFiddle Demo