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 lt first lt 5   and  1 lt second lt 3  and  2 lt third lt 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

User · Answer

Use   substring index  column      1     gt  first value substring index substring index  column      -2      1   gt  second value substring index substring index  column      -1      1   gt  third value   in your where clause   SELECT   FROM  table  WHERE  substring index  column      1  lt 0  AND substring index  column      1  gt 5

User · Answer

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

User · Answer

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

User · Answer

Usually substring index does what you want   mysql gt  select substring index  foo gmail com      -1    -----------------------------------------    substring index  foo gmail com      -1     -----------------------------------------    gmail com                                  -----------------------------------------  1 row in set  0 00 sec

User · Answer

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

User · Answer

You may get what you want by using the MySQL REGEXP or LIKE   See the MySQL Docs on Pattern Matching

User · Answer

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

[mysql] Can Mysql Split a column?

Examples related to mysql

Examples related to split