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

Question

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?

I don't think this is possible in MySQL directly. Would code for a .NET console app, or perhaps web based such as PHP or C# be helpful? — jiy, Oct 22 '14 at 17:08
do you have to do it inside the MYSQL query, or can you minipulate the data after? http://stackoverflow.com/questions/4311156/how-to-separate-letters-and-digits-from-a-string-in-php — Rich701, Oct 22 '14 at 17:34

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 22 '14 at 17:14

John Ruddell

25,283
6
57
86

that seems good but the names are more complex. may start by digit or not, may end with num or not. – MAli Fahimi Oct 22 '14 at 17:20
@MAliFahimi you can run this query twice once with the replace and once without and store the values in a temp table to concat all together – John Ruddell Oct 22 '14 at 17:21
@MAliFahimi if the names are more complex then why not provide the correct information for us to write a query off of? – John Ruddell Oct 22 '14 at 17:29
Thanks to all, your answers are so great,but i think it is better to do it in application layer...+1 for @John Ruddell – MAli Fahimi Oct 23 '14 at 16:33
@MAliFahimi it will be better to do it in the application layer. however, can you mark one of the answers as accepted to close this question? its also better for the community :) it will be greatly appreciated. – John Ruddell Nov 24 '14 at 21:43

score 3 · Answer 2 · answered Oct 22 '14 at 17:19

3

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 <> '' ;

answered Oct 22 '14 at 17:19

Abhik Chakraborty

44,654
6
52
63

I like your usage of LEAST on the SUBSTRING very cool... +1 from me... but now the OP said that the name can start with a number too :/ – John Ruddell Oct 22 '14 at 17:33
1

@JohnRuddell using this approach its good for a specific pattern but for mixed of patterns its not suitable. But I liked yours +1 – Abhik Chakraborty Oct 22 '14 at 18:18
+1 for @Abhik Chakraborty for your help. I learn new things about SQL – MAli Fahimi Oct 23 '14 at 16:35

score 0 · Answer 3 · answered Nov 28 '14 at 18:59

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

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

3 Answers3

EDIT: