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

Splitting Strings Using Delimiter Character in T-SQL

This question addresses the need to extract specific information from a long string stored in a table column. The challenge lies in splitting the string based on certain delimiters and extracting a particular piece of data.

To accomplish this using T-SQL, the following approach can be employed:

Firstly, the CHARINDEX or PATINDEX function is used to locate the position of the delimiter character ('|') within the string.

Next, the STUFF function is used to create a new string that includes the text after the delimiter character.

Lastly, the SUBSTRING function is utilized to extract the desired text, such as the "Client Name" in this example, based on the position of the delimiter character and the specified length of characters.

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

This query leverages the CHARINDEX or PATINDEX function to find the position of the "|" character within the "col3" column and then creates a new string that includes the text after the delimiter using the STUFF function. Finally, the SUBSTRING function is employed to extract the "Client Name" value based on the position and length of characters.

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