Search code examples
sqlt-sqlsql-server-2008

tsql last "occurrence of" inside a string

I have got field containing comma separated values. I need to extract the last element in the list. I have tried with this:

select list_field, LTRIM(RTRIM(right(list_field, len(list_field) - CHARINDEX(',',list_field))))

But it returns the last part of the list just starting after the first comma occurrence. For example,

a,b returns b

a,b,c returns b,c

I would like to use a regex like pattern. Is it possible in TSQL (sql server 2008)? Any other clues?

Solution

  • Find the last , by reversing the string and looking for the first occurrence, then read that many characters from the right of the string;

    rtrim(right(list_field, charindex(',', reverse(list_field)) - 1))

    (Use reverse(list_field) + ',' if there is the possibility of no delimiters in the field & you want the single value)