How to Split Comma-Separated Strings in SQL Server 2008 R2?

Efficiently Parsing Comma-Separated Strings in SQL Server 2008 R2

Working with comma-separated strings in SQL Server 2008 R2 often requires splitting them into individual elements. While some online solutions fall short, this custom function provides a reliable method:

<code class="language-sql">CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX))
RETURNS @returnList TABLE ([Name] [nvarchar](500))
AS
BEGIN
  DECLARE @name NVARCHAR(255), @pos INT;
  WHILE CHARINDEX(',', @stringToSplit) > 0
  BEGIN
    SELECT @pos = CHARINDEX(',', @stringToSplit);
    SELECT @name = SUBSTRING(@stringToSplit, 1, @pos - 1);
    INSERT INTO @returnList SELECT @name;
    SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) - @pos);
  END
  INSERT INTO @returnList SELECT @stringToSplit;
  RETURN;
END;</code>

This function, dbo.splitstring, takes a comma-separated string as input and returns a table where each row contains a single element. To use it:

<code class="language-sql">SELECT * FROM dbo.splitstring('91,12,65,78,56,789');</code>

This query will effectively separate the input string into its constituent parts, providing a practical solution for handling comma-delimited data within SQL Server 2008 R2.

The above is the detailed content of How to Split Comma-Separated Strings in SQL Server 2008 R2?. For more information, please follow other related articles on the PHP Chinese website!