Menu
To split a string in MySQL, you need to make use of the SUBSTRING_INDEX function that is provided by MySQL. The SUBSTRING_INDEX() function allows you to extract a part of a complete string. The syntax of the function is as follows: SUBSTRING_INDEX(expression, delimiter, count);
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
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
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.
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)If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With