When working with SQL Server databases, you may often encounter scenarios where you need to extract specific parts of a string based on a pattern. A common requirement is to retrieve the substring that follows the last underscore (_) in a given string. This blog post will demonstrate how to achieve this using SQL Server functions.

Example Data

Let’s consider a table named example_data with a column named data_string containing the following values:

• DATA_CC_001_REPORT
• DATA_CC_002_SUMMARY
• DATA_CC_003_DETAILS
• DATA_CC_004_OVERVIEW

Our goal is to extract the substrings REPORT, SUMMARY, DETAILS, and OVERVIEW from the above strings.

SQL Query to Extract Substrings

To extract the part of the string that follows the last underscore, we can use the REVERSE, CHARINDEX, and SUBSTRING functions in SQL Server. Here’s how the query looks:

SELECT
    data_string,
    REVERSE(SUBSTRING(REVERSE(data_string), 1, CHARINDEX('_', REVERSE(data_string)) - 1)) AS extracted_value
FROM
    example_data;

Explanation of the Query

1. REVERSE Function:
   • REVERSE(data_string): This reverses the original string.
2. CHARINDEX Function:
   • CHARINDEX('_', REVERSE(data_string)): This finds the position of the first underscore in the reversed string, which corresponds to the position of the last underscore in the original string.
3. SUBSTRING Function:
   • SUBSTRING(REVERSE(data_string), 1, CHARINDEX('_', REVERSE(data_string)) - 1): This extracts the substring from the start of the reversed string up to the position before the first underscore.
4. REVERSE Function:
   • REVERSE(SUBSTRING(...)): This reverses the extracted substring back to its original order.

Full Example

Let’s say our example_data table is defined as follows:

CREATE TABLE example_data (
    data_string VARCHAR(255)
);

INSERT INTO example_data (data_string) VALUES
('DATA_CC_001_REPORT'),
('DATA_CC_002_SUMMARY'),
('DATA_CC_003_DETAILS'),
('DATA_CC_004_OVERVIEW');

Running the query will produce the following result:

data_string	extracted_value
DATA_CC_001_REPORT	REPORT
DATA_CC_002_SUMMARY	SUMMARY
DATA_CC_003_DETAILS	DETAILS
DATA_CC_004_OVERVIEW	OVERVIEW

Conclusion

Using SQL Server’s string functions, you can easily extract specific parts of strings based on patterns. The approach demonstrated here for extracting the substring after the last underscore can be adapted to various other patterns and requirements.

Feel free to adapt the provided query to suit your specific needs. If you have any questions or need further assistance, leave a comment below!