How to Find the Last Occurrence of a Character in SQL Server?

SQL Server string operation: efficiently find the last occurrence of a character

Accessing the last index of a substring is critical in various database operations. Although SQL Server does not have a direct LastIndexOf function, there are some clever ways to achieve this functionality.

A common workaround is to reverse the string and use CHARINDEX to search for the last occurrence of a character. However, this approach can be cumbersome and less readable.

Fortunately, SQL Server 2016 introduces some new functions that simplify this task:

Get the substring after the last occurrence of a character:

To get the substring after the last occurrence of a character, you can use the following formula:

select right(db_name(), charindex('_', reverse(db_name()) + '_') - 1)

This expression first reverses the string and then uses CHARINDEX to find the last character. Then, it applies the RIGHT function to extract the substring.

Get the substring before the last occurrence of a character:

Alternatively, if you want the substring before the last occurrence of a character, you can use the following syntax:

select left(db_name(), len(db_name()) - charindex('_', reverse(db_name()) + '_'))

Here, the LEN function determines the total length of the string while the LEFT function is used to extract the required substring.

By using these techniques, you can manipulate strings in SQL Server 2016 more efficiently, making complex operations more straightforward and easier to read.

The above is the detailed content of How to Find the Last Occurrence of a Character in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!