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
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;
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;
Get 4 character substring from beginning, convert implicitly to number, get number with lowest i.
REPLACE all non-digit characters with blanks and use LEFT(..., 4) + 0 on result. Unfortunately MySQL does not support REGEX_REPLACE.Using locate(), least(), and substr()
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)
