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
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.
According to all the description I wrote, the next code ended working for me. A lot of hints are hiding in the description, but if you have any suggestion about the query, be my guest.
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'
a double quote is a distinct character not simply 2 single quotes so to strip off the double quotes try.
SELECT REPLACE(model_name,'"','') FROM ProductList
you are sooo close with the code above. given that your numeric portion will always be 4 chars you could use something like this
declare @t as table(field varchar(15))
insert into @t
values
('"ABC1234"'),
('"ABC5678/DEF"'),
('"AB1298"'),
('"AB1298/DEF"')
select left(val,2) as lval, right(val,2) as rval
from
(select SUBSTRING(field, PATINDEX('%[0-9]%', field), 4) val
from @t) t
so with the context of your database it would be something like
select left(val,2) as lval, right(val,2) as rval
from
(select SUBSTRING(model_name, POSITION('%[0-9]%', model_name), 4) val
from ProductList) p
To split the numeric and the string part of your values you can use trim, like this:
SELECT trim(field, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ"') AS col0, trim(field, '1234567890"') AS col1 FROM table
Hope it help.
P.S.
This solution also work on sqlite database where the function PATINDEX doesn't exisit.