CODEWITHSUNDEEP
mysqlregexregexp-substr
MAli Fahimi
MAli Fahimi

Reputation: 195

how can extract part of a text from a field in mysql?

I have fields like this:

-----------------
   id    |  name  
-----------------
   1     |  name123  
-----------------
   2     |  name  
-----------------
   3     |  name456  
-----------------
   4     |  name

I want to extract rows which have digit in name and a field that contains the number like this

------------------------------
   id    |  name     | number
-----------------------------
   1     |  name123  |  123
-----------------------------
   3     |  name456  |  456

how can we find the records that have digit and extract digit as a new field?

Upvotes: 2

Views: 1218

Answers (3)

Gary_W
Gary_W

Reputation: 10360

Another way, assuming the number you want is at the end of the string. REVERSE() to put the number part in front, then CONVERT() to make it a number and strip off the text, then REVERSE() again WHERE name ends in a number. Feels like a kludge though:

select id, name, reverse(convert(reverse(name),signed ))
from tbl
where name REGEXP '[0-9]+$';

SQL Fiddle Example

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

you can use MySQL's string conversion on an int to strip out the name like so

SELECT 
    t.id, 
    t.name, 
    REVERSE(REVERSE(t.name)+ 0) AS num,
    REPLACE(t.name,REVERSE(REVERSE(t.name)+ 0),'') AS actualname
FROM foobar t
HAVING num <> 0

the trick with this is by adding a 0 mysql is comparing the numeric value in the name... however the name has to start with a number... so I reverse it do the calculation and then reverse again... NOTE all of your names have to start with the name and end with a number for this to work for all of them

FIDDLE DEMO

EDIT:

since you say that some can start with a number and others end with a number.. then try this

SELECT 
    t.id, 
    t.name, 
    REVERSE(REVERSE(t.name)+ 0) AS num,
    REPLACE(t.name,REVERSE(REVERSE(t.name)+ 0),'') AS actualname
FROM foobar t
HAVING num <> 0
UNION ALL
SELECT 
    t.id, 
    t.name, 
    t.name + 0 AS num,
    REPLACE(t.name,t.name + 0,'') AS actualname
FROM foobar t
HAVING num <> 0

ANOTHER DEMO

Upvotes: 3

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Here is another way to do with mysql

SELECT 
  id,
  name,
  SUBSTRING( 
    name,LEAST (
      if (Locate('0',name) >0,Locate('0',name),999),
      if (Locate('1',name) >0,Locate('1',name),999),
      if (Locate('2',name) >0,Locate('2',name),999),
      if (Locate('3',name) >0,Locate('3',name),999),
      if (Locate('4',name) >0,Locate('4',name),999),
      if (Locate('5',name) >0,Locate('5',name),999),
      if (Locate('6',name) >0,Locate('6',name),999),
      if (Locate('7',name) >0,Locate('7',name),999),
      if (Locate('8',name) >0,Locate('8',name),999),
      if (Locate('9',name) >0,Locate('9',name),999)
    ),LENGTH(name)
  ) as number
from users 
having number <> '' ;

Upvotes: 3

Related Questions