Parse text using substring in mysql

Question

I want to parse a text using substring. The format we have for the text is like this:

N, Adele, A, 18

And the substring we do is like this:

SUBSTRING_INDEX(SUBSTRING_INDEX(text, ',', 2), ', ', -1) as 'Name', 
SUBSTRING_INDEX(SUBSTRING_INDEX(text, ',', 4), ', ', -1) as 'Age',

The output we get is:

|   Name    |   Age |
|   Adele   |   18  |

But we want to change the text format to:

N Adele, A 18

What would be the correct syntax so can I parse the text in the position 1 (N Adele) and use the delimiter space and just get Adele? And then same for the next text (A 18)?

I tried doing

SUBSTRING_INDEX(SUBSTRING_INDEX(text, ' ', 1), ', ', -1) as 'Name',

But the output I got is just

|   Name    |
|   N       |

The output I was hoping for is like this:

|   Name    |
|   Adele   |

What is the desired output that you're after? Is it N Adele, A 18 or is it the same as the output you're already getting? I'm unsure. — scrowler
– scrowler, Commented Aug 13, 2014 at 23:04
@scrowler On the text N Adele, A 18 I want to get the output Adele and 18 — sleepsleepsleep90731
– sleepsleepsleep90731, Commented Aug 13, 2014 at 23:05
Yes that's just field names and is not important. On the text N Adele I just want to get the Adele part of that text — sleepsleepsleep90731
– sleepsleepsleep90731, Commented Aug 13, 2014 at 23:13

scrowler · Accepted Answer · 2014-08-13 23:23:38Z

1

Presuming here that you want to change your original data structure and still be able to get the results out. You change your data structure to:

N Adele, A 18 -- etc

With the potential to have multiple names as the name (space separated), my previous example is not correct.

You could trim off the N and A directly with their space, knowing that they will only ever be two characters long and that they will always be there, like this:

SUBSTRING(TRIM(SUBSTRING_INDEX(`text`, ',', 1)), 3) AS 'Name',
SUBSTRING(TRIM(SUBSTRING_INDEX(`text`, ',', -1)), 3) AS 'Age'

To get:

Name   |   Age
--------------------
Adele  |   18

edited Aug 13, 2014 at 23:23

answered Aug 13, 2014 at 23:10

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sleepsleepsleep90731 Over a year ago

Ah wait, what if the text is like N Adele Gaga is it still going to give the output Adele Gaga ?

scrowler Over a year ago

Use @leon's answer - his is better. I've added another example for you anyway though.

planestepper · Accepted Answer · 2014-08-13 23:09:46Z

1

You can use

SELECT
  SUBSTRING(text, 2, INSTR(text, ',') - INSTR(text, ' ')) AS Name,
  SUBSTRING(text, INSTR(text, ',') + 3, LENGTH(text) - INSTR(text, ',') + 3) AS Age
FROM your_table;

as the position of the field descriptors (N and A) are fixed (relative to the start of the string and to the comma). You can check the working query in this fiddle.

answered Aug 13, 2014 at 23:09

planestepper

3,32729 silver badges40 bronze badges

Collectives™ on Stack Overflow

Parse text using substring in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related