Reputation: 1013
I have a comma separated string like
1,2,3,4,5
or
1,2,3,4,5,5,8
or 1,2,2,6,6,7,8
I want anything that is between the third comma and last comma leaving out the last element.
The result of 1,2,3,4,5
should be 4
.
The result of 1,2,3,4,5,5,8
should be 4,5,5
.
The result of 1,2,2,6,6,7,8
should be 6,6,7
.
I tried with the below. But it fails when the 3rd value is same as 1st or 2nd value.
SUBSTRING( LEFT(string, LENGTH(string)
- LOCATE(',', REVERSE(string)))
, LOCATE(SUBSTRING_INDEX(SUBSTRING_INDEX(string, ',', 3), string ))
Upvotes: 0
Views: 99
Reputation: 1013
Finally this is what I did to resolve. Comments are welcome.
select SUBSTRING(@String,length(SUBSTRING_INDEX(@String,',',3))+2,
(length(@String) - length(SUBSTRING_INDEX(@String,',',-1))-length(SUBSTRING_INDEX(@String,',',3)) -2));
Upvotes: 0
Reputation: 1271241
Hmmmm. To get rid of the last element:
select substr(string, 1, length(string) - substring_index(string, ',', -1) - 1)
Then to get rid of the first three:
select substr(substr(string, 1, length(string) - substring_index(string, ',', -1) - 1),
substring_index(string, ',', 3) + 1
)
Upvotes: 2