Can MySQL split a column?

Question

I have a column that has comma separated data:

1,2,3
3,2,1
4,5,6
5,5,5

I'm trying to run a search that would query each value of the CSV string individually.

0<first<5   and  1<second<3  and  2<third<4

I get that I could return all queries and split it myself and compare it myself. I'm curious if there is a way to do this so MySQL does that processing work.

Thanks!

score 53 · Answer 1 · answered Jun 13 '12 at 05:14

53

Use

substring_index(`column`,',',1) ==> first value
substring_index(substring_index(`column`,',',-2),',',1)=> second value
substring_index(substring_index(`column`,',',-1),',',1)=> third value

in your where clause.

SELECT * FROM `table`
WHERE 
substring_index(`column`,',',1)<0 
AND
substring_index(`column`,',',1)>5

answered Jun 13 '12 at 05:14

raj karthy

31
2
2

3

I had to use substring_index(substring_index(`column`,',',-1),',',1) to get the second value. Using -2 consistently gave me the first value. Worked great, though. :) – Benjamin Oman Jan 10 '13 at 00:28
The indices in this answer get a bit confusing as you add more rows. – Tom Nov 10 '17 at 10:03
Great solution. Worked. Thanks! – Flavio Jul 16 '19 at 12:17

score 35 · Answer 2 · edited Jun 11 '20 at 09:05

It seems to work:

substring_index ( substring_index ( context,',',1 ), ',', -1) 
substring_index ( substring_index ( context,',',2 ), ',', -1)
substring_index ( substring_index ( context,',',3 ), ',', -1)
substring_index ( substring_index ( context,',',4 ), ',', -1)

it means 1st value, 2nd, 3rd, etc.

Explanation:

The inner substring_index returns the first n values that are comma separated. So if your original string is "34,7,23,89", substring_index( context,',', 3) returns "34,7,23".
The outer substring_index takes the value returned by the inner substring_index and the -1 allows you to take the last value. So you get "23" from the "34,7,23".
Instead of -1 if you specify -2, you'll get "7,23", because it took the last two values.

Example:

select * from MyTable where substring_index(substring_index(prices,',',1),',',-1)=3382;

Here, prices is the name of a column in MyTable.

This is the clearer answer given it's indices are more structured. — Tom, Nov 10 '17 at 10:04

score 12 · Answer 3 · answered Aug 07 '13 at 11:34

Usually substring_index does what you want:

mysql> select substring_index("foo@gmail.com","@",-1);
+-----------------------------------------+
| substring_index("foo@gmail.com","@",-1) |
+-----------------------------------------+
| gmail.com                               |
+-----------------------------------------+
1 row in set (0.00 sec)

score 4 · Answer 4 · answered Jul 08 '09 at 08:04

4

You may get what you want by using the MySQL REGEXP or LIKE.

See the MySQL Docs on Pattern Matching

answered Jul 08 '09 at 08:04

Damo

11,410
5
57
74

score 1 · Answer 5 · answered Apr 14 '13 at 22:12

Here is another variant I posted on related question. The REGEX check to see if you are out of bounds is useful, so for a table column you would put it in the where clause.

SET @Array = 'one,two,three,four';
SET @ArrayIndex = 2;
SELECT CASE 
    WHEN @Array REGEXP CONCAT('((,).*){',@ArrayIndex,'}') 
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(@Array,',',@ArrayIndex+1),',',-1) 
    ELSE NULL
END AS Result;

SUBSTRING_INDEX(string, delim, n) returns the first n
SUBSTRING_INDEX(string, delim, -1) returns the last only
REGEXP '((delim).*){n}' checks if there are n delimiters (i.e. you are in bounds)

Merijn Vogel · Answer 6 · 2010-12-15T11:39:08.160

1

As an addendum to this, I've strings of the form: Some words 303

where I'd like to split off the numerical part from the tail of the string. This seems to point to a possible solution:

http://lists.mysql.com/mysql/222421

The problem however, is that you only get the answer "yes, it matches", and not the start index of the regexp match.

edited Dec 15 '10 at 11:39

answered Dec 15 '10 at 11:33

Merijn Vogel

1
2

score 0 · Answer 7 · answered Oct 25 '22 at 15:16

Building on @Oleksiy's answer, here is one that can work with strings of variable segment lengths (within reasonable limits), for example comma-separated addresses:

SELECT substring_index ( substring_index ( address,',',1 ), ',', -1) AS address_line_1,
   IF(address_parts > 1, substring_index ( substring_index ( address,',',2 ), ',', -1), '') AS address_line_2,
   IF(address_parts > 2, substring_index ( substring_index ( address,',',3 ), ',', -1), '') AS address_line_3,
   IF(address_parts > 3, substring_index ( substring_index ( address,',',4 ), ',', -1), '') AS address_line_4,
   IF(address_parts > 4, substring_index ( substring_index ( address,',',5 ), ',', -1), '') AS address_line_5
FROM (
    SELECT address, LENGTH(address) - LENGTH(REPLACE(address, ',', '')) AS address_parts
      FROM mytable
) AS addresses

score -2 · Answer 8 · answered Apr 08 '13 at 12:30

It's working..

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(col,'1', 1), '2', 1), '3', 1), '4', 1), '5', 1), '6', 1)
, '7', 1), '8', 1), '9', 1), '0', 1) as new_col  
FROM table_name group by new_col;

Can MySQL split a column?

8 Answers8

Explanation:

Example:

Linked

Related