How Can I Extract Specific Values from Delimited Strings in T-SQL?

Splitting Strings Using Delimiters in T-SQL

In T-SQL, extracting specific information from long strings can be achieved by splitting the string using delimiter characters. This technique proves particularly useful when dealing with complex, structured data.

Example Scenario

Consider a table with a column containing a string with multiple key-value pairs separated by pipe (|) and equal (=) characters. The goal is to retrieve only the specific value associated with "Client Name."

Solution Using SUBSTRING and STUFF Functions

To extract the desired value, we can utilize a combination of the SUBSTRING and STUFF functions:

Select col1, col2, LTRIM(RTRIM(SUBSTRING(
    STUFF(col3, CHARINDEX('|', col3,
    PATINDEX('%|Client Name =%', col3) + 14), 1000, ''),
    PATINDEX('%|Client Name =%', col3) + 14, 1000))) col3
from Table01

Breakdown of the Solution

The query employs the following steps:

1. STUFF Function: Adds a space character at the specified location to create a unique delimiter for the key-value pair containing "Client Name."
2. CHARINDEX Function: Finds the position of the pipe character that precedes "Client Name."
3. PATINDEX Function: Identifies the position of the "Client Name" key-value delimiter.
4. SUBSTRING Function: Extracts the substring containing the "Client Name" value.
5. LTRIM and RTRIM Functions: Remove any leading or trailing whitespace from the extracted value.

Performance Considerations

While both CharIndex and PatIndex can be used for delimiter identification, performance tests indicate that they yield similar results. Therefore, the choice between them is often preferential.

The above is the detailed content of How Can I Extract Specific Values from Delimited Strings in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!