In this post, I will explain you about Split and convert Comma Separated String. In this article I will explain with example code, how to split and convert a comma separated / delimited string to a table using Split function in SQL Server 2005, 2008, 2012 and MSSQL 2014 versions. The string containing words or letters or numbers separated (delimited) by comma or underscore or plus(+) ,etc,. are going to be split into Table values.
I will conjointly explain a way to use the Split function to separate a string in an exceedingly SQL query or stored Procedures in SQL Server 2005, 2008 and 2012 versions. And here is the code that I used:
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
Now, this is the example 1:
SELECT * FROM [SplitString]('Apple-Dell-HP-Lenovo-Sony','-')
Now, write the example 2:
SELECT * FROM [SplitString]('Apple,Dell,HP,Lenovo,Sony',',')
Here is the output from the code above:
Example code 3:
SELECT * FROM [SplitString]('1,2,3,4,5')
The output of the above code snippet is shown on the following picture:
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.