How Can I Split a Delimited String in SQL Server and Access Individual Items?

Split delimited string in SQL Server: accessing individual items

Splitting a string by delimiter is a common task in SQL Server. However, unlike some other programming languages, SQL Server does not provide built-in functions specifically for string splitting.

One workaround is to use the PARSENAME function, which typically parses a path or filename into its component parts. We can use PARSENAME to split a string into individual items by strategically replacing spaces in the string with periods.

For example, to split the string "Hello John Smith" by spaces and access the item at index 1 (which would return "John"), we could use the following query:

<code class="language-sql">SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)</code>

PARSENAME splits the string based on the period delimiter and returns the second segment, which is "John".

This method has limitations. If the string already contains periods, this may result in unexpected splitting. Therefore, it is often recommended to define a user-defined function (UDF) specifically for string splitting to avoid potential complications.

The above is the detailed content of How Can I Split a Delimited String in SQL Server and Access Individual Items?. For more information, please follow other related articles on the PHP Chinese website!