[SOLVED] How to extract value between two commas in string with multiple commas?

How to extract value between two commas in string with multiple commas?

I've got a table in Sybase that has a column - comments - where values consist of multiple commas.

Example:

SURNAME,NAME,DATE,AMOUNT,ERROR,STATUS

I want to extract just 'ERROR', in this case the second to last value. I've been able to extract the last value using the below query but moving it to just return the second-to-last only is proving difficult:

My Query:

right(comments,charindex(',',reverse(comments)+',')-1)

Any help would be appreciated

Solution

ASE 16 limits the choices, I suggest using a "derived table" (subquery) to reverse the comments column as we need to use this more than once to get the positions of the last and second last commas.

SELECT 
    SUBSTRING(
        d.comments,
        CHARINDEX(',', d.rev_comments, CHARINDEX(',', d.rev_comments) + 1) + 1,
        CHARINDEX(',', d.rev_comments) - 1
    ) AS second_last_portion
FROM (
    SELECT
          *
        , REVERSE(comments) AS rev_comments
    FROM your_table
    ) AS d

nb: ASE 16 doesn't support REGEXP_SUBSTR() or lateral joins or apply operator - which would otherwise be useful to solve for this type of problem.