substring in MySQL

Question

I am trying to insert a string in MySQL table. But the following is not what I am looking for.

select substring_index('3|5|6|asdf asd|6|0|NULL', '|', 1) as first,
substring_index('3|5|6|asdf asd|6|0|NULL', '|', 2) as second,
substring_index('3|5|6|asdf asd|6|0|NULL', '|', 3) as third,
substring_index('3|5|6|asdf asd|6|0|NULL', '|', 4) as forth,
substring_index('3|5|6|asdf asd|6|0|NULL', '|', 5) as fifth

+-------+--------+-------+----------------+------------------+
| first | second | third | forth          | fifth            |
+-------+--------+-------+----------------+------------------+
| 3     | 3|5    | 3|5|6 | 3|5|6|asdf asd | 3|5|6|asdf asd|6 | 
+-------+--------+-------+----------------+------------------

+

I want 5 as second, 6 as third and 'asdf asd' as forth column. Is it possible in MySQL?

Answer 1

It sounds to me like you are missing to specify the index at which you are getting the sub-strings, hence you are always starting at the beginning of the original string. I am not very conversant with the mysql syntax, but check if the substring_index function supports this.

---

SUBSTRING_INDEX(str,delim,count) Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com' This function is multi-byte safe. From here

Answer 2

substring_index(substring_index('3|5|6|asdf asd|6|0|NULL', '|', 2), '|', -1)

gives 5

substring_index(substring_index('3|5|6|asdf asd|6|0|NULL', '|', 3), '|', -1)

gives 6

and so on

Answer 3

You could split it on the delimiter. Mysql lacks a function to do this, but you could create one:

create function split_string(stringy varchar(128), lim varchar(3),posi int)
  returns varchar(255)
  return replace(substring(substring_index(stringy, lim, posi),
    length(substring_index(stringy, lim, posi -1)) + 1), lim, '');

for simplicity let's set a var to '3|5|6|asdf asd|6|0|NULL'...

set @cow='3|5|6|asdf asd|6|0|NULL';
select split_string(@cow,'|',1) as first,split_string(@cow,'|',2) as second,
split_string(@cow,'|',3) as third,split_string(@cow,'|',4) as fourth,
split_string(@cow,'|',5) as fifth,split_string(@cow,'|',6) as sixth;