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!
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;
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 nSUBSTRING_INDEX(string, delim, -1)
returns the last onlyREGEXP '((delim).*){n}'
checks if there are n delimiters (i.e. you are in bounds)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.
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.
select * from MyTable where substring_index(substring_index(prices,',',1),',',-1)=3382;
Here, prices
is the name of a column in MyTable
.
Usually substring_index does what you want:
mysql> select substring_index("[email protected]","@",-1);
+-----------------------------------------+
| substring_index("[email protected]","@",-1) |
+-----------------------------------------+
| gmail.com |
+-----------------------------------------+
1 row in set (0.00 sec)
You may get what you want by using the MySQL REGEXP or LIKE.
See the MySQL Docs on Pattern Matching
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
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.
Source: Stackoverflow.com