Problem
I have a field with values like
"ABC1234"
"ABC5678/DEF"
"AB1298"
"AB1298/DEF"
I want to extract the numeric value from each one of it, such as:
1234
5678
1298
1298
NOTE: The numeric value is always "together" (1234) and is always composed by 4 digits only.
I was trying to delete first the double quotes to use RegEx: SELECT REPLACE(model_name,'''','') FROM ProductList Note: I replace using ' single quote instead of double quotes " , because that's the way the data was saved, and it works. And then I tried to use Patindex to get the numeric value: SELECT SUBSTRING(field, PATINDEX('%[0-9]%', field), LEN(field)) NOTE: However, PATINDEX does not work with MySQL
I'm trying to do this, because then I want to separate each value in two different columns by creating a temporary table:
SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one, SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two FROM YOUR_TABLE t
val1 val2
12 34
56 78
12 98
Note: I'm using PHPMyAdmin within XAMPP.
After PATINDEX, I tried LOCATION, and POSITION. (You can see the sequence of the test print screen here ) SELECT SUBSTRING(model_name,LOCATE('%[^0-9]%',model_name),4) FROM ProductList NOTE: The LOCATE or POSITION function is returning a position 0, and that's why there is no result. I can imagine the problem is in "%[^0-9]%", because not LOCATE, POSITION nor MID accept RegEx.
My next problem is: I want to have the lval and rval inside the temporary table footprint, created at the beginning of the query. This, because I would like to create queries by getting input texts values, and have something like:
SELECT * FROM footprint WHERE lval=50;
model_name num_pos lval rval
''ABC1234'' 7 12 34
''ABC1234/DEF'' 7 50 78
''ABDCE1234'' 8 12 98
Solution Proposal
At the end, I want to search the name_model, depending on the values of lval and rval. So the next "query" works for me:
CREATE TEMPORARY TABLE IF NOT EXISTS footprint AS
(SELECT model_name,
LEAST (
if (Locate('0',model_name) >0,Locate('0',model_name),999),
if (Locate('1',model_name) >0,Locate('1',model_name),999),
if (Locate('2',model_name) >0,Locate('2',model_name),999),
if (Locate('3',model_name) >0,Locate('3',model_name),999),
if (Locate('4',model_name) >0,Locate('4',model_name),999),
if (Locate('5',model_name) >0,Locate('5',model_name),999),
if (Locate('6',model_name) >0,Locate('6',model_name),999),
if (Locate('7',model_name) >0,Locate('7',model_name),999),
if (Locate('8',model_name) >0,Locate('8',model_name),999),
if (Locate('9',model_name) >0,Locate('9',model_name),999)
) AS num_Pos
FROM ProductList) ;
SELECT name FROM (SELECT name, left(val,2) AS lval, right(val,2) AS rval FROM
(SELECT MID(pl.model_name, fp.num_Pos,4) AS val, pl.model_name AS name FROM ProductList AS pl INNER JOIN footprint AS fp ON fp.model_name=pl.model_name) p) n WHERE lval='50' and rval='50'
If you have any other suggestion about how this process could be done or improved, please let me know.
Thank you, Best regards.
