MySQL select field from string

Question

I have a table with 3 columns:

number | number2 | string
________________________________________________________________
     1 |       2 | 0 423 0 2 0 4 2 1 423 521

So, how to get a result like this? :

1, 2, 423

Where 423 is the second number in the string?

I'm guessing you will have to use your programming language to parse that string and pull out the second number. What language are you using? — dmikester1
– dmikester1, Commented Sep 30, 2014 at 18:25
Do you mean second highest number? (OR) second positional number? — Rahul
– Rahul, Commented Sep 30, 2014 at 18:28

Mureinik · Accepted Answer · 2014-09-30 18:29:31Z

1

mysql doesn't have a built-in function to split strings, but could MacGyver something up with substring_index:

SELECT `number`, `number2`, 
       SUBSTRING_INDEX(SUBSTRING_INDEX(`string`, ' ', 2), ' ', -1)
FROM   my_table

answered Sep 30, 2014 at 18:29

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

Joachim Isaksson · Accepted Answer · 2014-09-30 18:34:25Z

0

You can always use string functions to remove the first value, and cast the rest to int;

SELECT number, 
       number2, 
       CAST(SUBSTR(string, LOCATE(' ', string)) AS SIGNED) number3
FROM mytable;

answered Sep 30, 2014 at 18:34

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges