How to Extract Specific Data from a Delimited String in T-SQL?

How to Isolate Specific Data from a Delimited String in T-SQL

In the world of SQL, working with strings can sometimes present challenges, especially when attempting to extract specific information from a longer string that contains multiple data points. Let's explore a scenario where you have a table with a column that holds a string containing multiple key-value pairs separated by a delimiter.

Problem Definition

Given a table with the following structure:

Col1 = '123'
Col2 = 'AAAAA'
Col3 = 'Clent ID = 4356hy|Client Name = B B BOB|Client Phone = 667-444-2626|Client Fax = 666-666-0151|Info = INF8888877 -MAC333330554/444400800'

Your goal is to extract only the "Client Name" value, which in this case is "B B BOB," from the "Col3" column. The delimiters separating the key-value pairs are '|' pipe characters, and the key-value pairs themselves are delimited by the equal sign (' = ') with leading and trailing white spaces.

Solution

To address this problem in T-SQL, you can employ the following string manipulation techniques:

1. CHARINDEX and SUBSTRING: Use the CHARINDEX function to locate the starting position of the "Client Name" key and substring the respective value from the original string.
2. STUFF: Use the STUFF function to insert a delimiter at the beginning of the substring to separate it from the rest of the string.
3. LTRIM and RTRIM: Use the LTRIM and RTRIM functions to remove any leading or trailing spaces from the extracted value.

Code Snippet

For your specific data, you can use the following code snippet to achieve the desired result:

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

Note: You may encounter a minor performance difference when using CHARINDEX versus PATINDEX to locate the starting position of the key. However, both approaches generally yield similar results, as their efficiencies are comparable.

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