Question 20354 in Mysql Question posted in
The official MySQL documentation can be found here.

How to extract only numbers from string in mysql

I have a data like under mentioned in table in one column name remarks

Will0017,Will0018, Kind0045 , DGRG0012

I want to extract only numbers from the same only like 0017,0018,0045,0012

How can I do the same in mysql.

I have tried like this

select reject_remarks,regexp_substr(reject_remarks,"[0-9]+")  from table;

but have anything like I want

I have tried like this

select reject_remarks,regexp_substr(reject_remarks,"[0-9]+")  from table ;
Tags:

3

Answers


  1. 0

    You current query shall work fine as in this DBFIDDLE

    select reject_remarks,regexp_substr(reject_remarks,"[0-9]+")  from mytable;

    It might have so happened that, you have syntax error by using reserved keywords like table

    Alternatively;
    You can use REGEXP_REPLACE in mysql for the same.

    SELECT reject_remark, REGEXP_REPLACE(reject_remarks, '[^0-9]+', '') AS numbers 
FROM table_name;

    This shall give you expected output as in this DBFIDDLE

    reject_remarks  numbers
Will0017        0017
Will0018        0018
Kind0045        0045
DGRG0012        0012
    Login or Signup to reply.
  2. 0

    you can use REGEXP_REPLACE where ou negate all possible allowed characters

    CREATE Table mytable(reject_remarks varchar(100))

    INSERT INTO mytable VALUES ('Will0017,Will0018, Kind0045 , DGRG0012')

    SELECT  REGEXP_REPLACE(reject_remarks, '[^0-9,[:space:]]+', '') AS numbers 
FROM mytable;
    numbers
    0017,0018, 0045 , 0012

    fiddle

    Login or Signup to reply.
  3. 0

    When using regexp_substr, you need to use the fourth parameter (see: docs):

    select regexp_substr('Will0017,Will0018, Kind0045 , DGRG0012','[0-9]+',1,x)
from (select 1 as x union all select 2 union all 
      select 3 union all select 4)x;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search