How Can I Find the Last Occurrence of a Substring in SQL Server?

Efficiently Finding the Final Substring in SQL Server

Working with strings in SQL Server often requires pinpointing the last instance of a particular substring. While functions like SUBSTRING and CHARINDEX are useful for substring extraction, a more concise approach exists for finding the final occurrence.

This task is elegantly handled by combining the RIGHT() and CHARINDEX() functions. To illustrate, let's extract the portion of the database name (DB_NAME()) following the last underscore:

<code class="language-sql">SELECT RIGHT(DB_NAME(), CHARINDEX('_', REVERSE(DB_NAME()) + '_') - 1)</code>

The query cleverly reverses the database name, locates the final underscore, and then extracts the characters to its right.

Conversely, to extract the text before the last underscore, utilize LEFT() and CHARINDEX():

<code class="language-sql">SELECT LEFT(DB_NAME(), LEN(DB_NAME()) - CHARINDEX('_', REVERSE(DB_NAME()) + '_'))</code>

This reverses the database name, identifies the last underscore, and returns the preceding characters.

These methods provide a cleaner and more efficient solution for identifying the final substring in SQL Server, avoiding convoluted string manipulation.

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