I have trouble to split string and get only numeric value those are in round bracket. I try substring_index but can't get success.
Column
prd_code
HWC-4054 (100 Pcs available)
HWC-7514 (125 pcs available)
HWC-1516 (total 80 pcs available)
HWC-8008 (80pcs available)
Required output
prd_code
100
125
Thank you.
Just now I tried to solve your issue please look on it :
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('HWC-4054 (100 Pcs available)','(',-1),' ',1)
This is only code snippet, you need to check its suitable for your project or not.
Please check below solutions using mysql function.
select prd_code,digits(SUBSTRING_INDEX( SUBSTRING_INDEX( prd_code , '(', -1 ), ')', 1)) as one from `your table`
use below mysql function :
DELIMITER |
CREATE FUNCTION digits( str CHAR(32) ) RETURNS CHAR(32)
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret CHAR(32) DEFAULT '';
DECLARE c CHAR(1);
IF str IS NULL
THEN
RETURN "";
END IF;
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF c BETWEEN '0' AND '9' THEN
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END |
DELIMITER ;
reference from this
digits function ? i just say without function it is not possible ?select SUBSTRING_INDEX(REPLACE(SUBSTRING(SUBSTRING_INDEX(ucase(pcode), '(', 2),LENGTH(SUBSTRING_INDEX(ucase(pcode), '(', 2 -1)) + 1),'(', ' '),'P',1) as prd_code from tbltest;
In this tbltest is table name and pcode is column in that table which contains prd_code value
You can use PATINDEX
SELECT SUBSTRING(Your_FieldName, PATINDEX('%[0-9]%', Your_FieldName), LEN(Your_FieldName))
PATINDEX does't exist.