tsql last "occurrence of" inside a string

sql-server-2008 tsql string-matching last-occurrence
Alberto De Caro picture Alberto De Caro · Feb 28, 2012 · Viewed 22.4k times · Source

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?

Answer

Alex K. picture Alex K. · Feb 28, 2012

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)

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More