SUBSTRING_INDEX(str, delim, count)
- str: the string to be split.
- delim: the delimiter.
- count: the position of the delimiter. A positive number means extract from left to right, a negative number means extract from right to left.
For example:
SELECT SUBSTRING_INDEX('100-200-300-400', '-', 1); -- output: '100'
SELECT SUBSTRING_INDEX('100-200-300-400', '-', 2); -- output: '100-200'
SELECT SUBSTRING_INDEX('100-200-300-400', '-', 3); -- output: '100-200-300'
SELECT SUBSTRING_INDEX('100-200-300-400', '-', 5); -- output: '100-200-300-400'
SELECT SUBSTRING_INDEX('100-200-300-400', '-', -1); -- output: '400'
SELECT SUBSTRING_INDEX('100-200-300-400', '-', -2); -- output: '300-400' If you want to take the value in the middle, for example, the second of 100-200-300-400, you can first take the count of 2, and then take the count of -1 from the right.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('100-200-300-400', '-', 2),'-', -1);
-- '200' SUBSTRING(str, start, length)
- str: the original string from which the substring is to be extracted.
- start: the position to start extracting (counting from 1).
- length: optional parameter specifying the length of the substring to be extracted. If not specified, all characters from the start position will be extracted.
SELECT SUBSTRING('100-200-300-400',5) -- '200-300-400'
SELECT SUBSTRING('100-200-300-400',-3) -- '400'
SELECT SUBSTRING('100-200-300-400',5,3) -- '200' LOCATE(substr, str, pos)
- substr: the substring to find.
- str: the original string in which to find the substring.
- pos: optional parameter, specify the position to start searching. If not specified, the search will start from the first character.
SELECT LOCATE('-','100-200-300-400'); -- 4
SELECT LOCATE('-','100-200-300-400',5); -- 8 String splitting can be performed manually in conjunction with string functions such as SUBSTRING() and LOCATE(). This method is suitable for cases where the number of separators is fixed.
SELECT SUBSTRING('100-200-300-400', 1, LOCATE('-', '100-200-300-400') - 1); -- '100'
SELECT SUBSTRING('100-200-300-400', LOCATE('-', '100-200-300-400') + 1); -- '200-300-400'
SELECT SUBSTRING('100-200-300-400', LOCATE('-', '100-200-300-400') + 1,3); -- '200'
0 Comments