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?

eof, I'm sure it's just a simplified example using mysql's feature of selecting data from no table (like selecting from dual in Oracle). — JAL
– JAL, Commented Oct 24, 2009 at 12:31

user187291 · Accepted Answer · 2009-10-24 12:15:43Z

6

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

answered Oct 24, 2009 at 12:15

user187291

54.1k19 gold badges98 silver badges127 bronze badges

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

1 Comment

Andomar Over a year ago

+1 See dev.mysql.com/doc/refman/5.1/en/… for the documentation of substring_index

JAL · Accepted Answer · 2009-10-24 12:28:11Z

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;

Julius A · Accepted Answer · 2009-10-24 12:23:50Z

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

Collectives™ on Stack Overflow

substring in MySQL

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related