Introduction

When working with SQL Server, you might often encounter scenarios where you need to extract a specific part of a string. For example, you might have a string in the format DATA_TYPE_001_SAMPLE and you want to extract the last part, SAMPLE. In this blog post, we will explore how to achieve this using SQL Server’s string manipulation functions.

---

Problem Statement

Consider the following examples:

1. DATA_TYPE_001_SAMPLE
2. DATA_TYPE_002_TEST
3. DATA_TYPE_003_EXAMPLE
4. DATA_TYPE_004_DEMO

You want to extract the last part of each string, which are SAMPLE, TEST, EXAMPLE, and DEMO, respectively.

---

Solution

To solve this problem, we can use a combination of SQL Server string functions such as REVERSE, REPLACE, and PARSENAME. Here’s a step-by-step guide on how to implement this:

1. Reverse the StringWe start by reversing the string to simplify the process of identifying the last part.
2. Replace the Underscore with a PeriodReplace underscores with periods to transform the string into a format that can be easily split.
3. Extract the Last Part Using PARSENAMEUse the PARSENAME function to split the string by periods and extract the last part.

---

SQL Query Example

Here’s the SQL query to extract the last part of the string:

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE(column_name), '_', '.'), 1)) AS LastPart
FROM 
    your_table;

• Explanation:
  • REVERSE(column_name): Reverses the string.
  • REPLACE(REVERSE(column_name), '_', '.'): Replaces underscores with periods.
  • PARSENAME(..., 1): Extracts the first part (last part of the original string) after splitting by periods.

Replace column_name with the actual name of your column and your_table with the name of your table.

---

Example in Practice

Let’s test this with our examples:

Example 1: DATA_TYPE_001_SAMPLE

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE('DATA_TYPE_001_SAMPLE'), '_', '.'), 1)) AS LastPart;

Output: SAMPLE

Example 2: DATA_TYPE_002_TEST

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE('DATA_TYPE_002_TEST'), '_', '.'), 1)) AS LastPart;

Output: TEST

Example 3: DATA_TYPE_003_EXAMPLE

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE('DATA_TYPE_003_EXAMPLE'), '_', '.'), 1)) AS LastPart;

Output: TEST

Example 4: DATA_TYPE_004_DEMO

SELECT 
    REVERSE(PARSENAME(REPLACE(REVERSE('DATA_TYPE_004_DEMO'), '_', '.'), 1)) AS LastPart;

Output: DEMO

Conclusion

Extracting the last part of a string in SQL Server is straightforward with the right combination of string functions. This technique is versatile and can be applied to various string manipulation tasks.