In order to select a substring, knowing its ordinal position relative to the delimiter characters, we need to write a query using two nested functions:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('AAA:BBB:CCC:DDD', ':', 4),':','-1') FROM mytable;
Here the variable quantity is the number 4 as the string index (counted from 1, not from zero!), the string itself (instead of it you can, of course, use the output of another function or a column name) and the delimiter (in our case it's a colon - ":")
For example, the query above will output:
DDD
And if we change the number 4 to, say, 3:
CCC
Note that the -1 in the first function does NOT CHANGE! I.e. it stays that way.
PS. The downside of this method is that if we select a substring by a number that doesn't exist, MySQL will still return the last found string (not an empty string and not NULL). Example: we select substring number 5 from the string "first:second:third" with ":" as the delimiter. But the fifth substring doesn't exist - the last one is the third. MySQL will still return "third" to us.
Comments