CODEWITHSUNDEEP
mysqlsqlstringselectsubstring
Acrivec
Acrivec

Reputation: 11

MySQL select field from string

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?

Upvotes: 1

Views: 329

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

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;

An SQLfiddle to test with.

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

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

Upvotes: 1

Related Questions