Wednesday, 8 October 2014

Split and convert Comma Separated String or any Delimited String to Table in SQL Server

Hi friends,in this article I will explain about How to Split and convert Comma Separated String or any Delimited String to Table in SQL Server
In this article I will explain with examples, how to split and convert a comma separated / delimited string to a table using Split function in SQL Server 2005, 2008 and 2012 versions. The string containing words or letters or numbers separated (delimited) by comma or underscore or  plus(+) ,etc,. will be split into Table values.
I will also explain how to use the Split function to split a string in a SQL Query or Stored Procedures in SQL Server 2005, 2008 and 2012 versions.

--Split and convert Comma Separated (Delimited) String to Table in SQL Server
CREATE FUNCTION [dbo].[SplitString] (@InputString NVARCHAR(MAX),@delimiter CHAR(1))
RETURNS @tbl TABLE (
  Item NVARCHAR(50) NOT NULL
)
AS
BEGIN
  DECLARE @StartIndex int,
          @NextIndex int,
          @ItemLen int

  SELECT
    @StartIndex = 0,
    @NextIndex = 1

  WHILE @NextIndex > 0
  BEGIN
    SELECT
      @NextIndex = CHARINDEX(@delimiter, @InputString, @StartIndex + 1)
    SELECT
      @ItemLen =
                CASE
                  WHEN @NextIndex > 0 THEN @NextIndex
                  ELSE LEN(@InputString) + 1
                END - @StartIndex - 1
    INSERT @tbl (Item)
      VALUES (CONVERT(varchar(50), SUBSTRING(@InputString, @StartIndex + 1, @ItemLen)))
    SELECT
      @StartIndex = @NextIndex
  END
  RETURN
END

Example 1:


 SELECT * FROM [SplitString]('Apple-Dell-HP-Lenovo-Sony','-')




Example 2:

SELECT * FROM [SplitString]('Apple,Dell,HP,Lenovo,Sony',',')

The output of the above two queries will be resulted as 
Example 3:

SELECT * FROM [SplitString]('1,2,3,4,5')


The output of the above Query is as shown in the below figure.
 

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.