CODEWITHSUNDEEP
mysqlsqlsubstring
suresh
suresh

Reputation: 1013

MySQL - Get the values between delimiters in a string

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

Answers (2)

suresh
suresh

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

Gordon Linoff
Gordon Linoff

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

Related Questions