How to Extract Specific Values from Delimited Strings in Transact-SQL?

Splitting Strings with Delimiters in Transact-SQL

When dealing with complex string values in a table, the need often arises to extract specific information. This can be achieved by using delimiters to define the structure of the string.

One such scenario is exemplified by a table with a column containing a string like:

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

Here, we have specific delimiters separating the key-value pairs: '|' to delineate columns and '= ' (including spaces) to separate keys and values. The objective is to extract the 'Client Name' value, in this case 'B B BOB'.

To achieve this, a combination of string manipulation functions and conditional statements can be employed. One approach involves using the CHARINDEX or PATINDEX functions to locate the position of the desired value in the string.

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

In this query, PATINDEX is used to find the starting position of 'Client Name =', followed by adding 14 to skip this portion. Then, CHARINDEX is employed to find the position of the next '|'. Finally, the SUBSTRING function extracts the desired value between these positions, and LTRIM and RTRIM are used to trim any leading or trailing spaces.

It's worth noting that the specific functions used for this task may vary depending on the complexity of the string and the desired outcome.

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