How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fnSplit](@sInputList VARCHAR(8000), @sDelimiter VARCHAR(8000) = ',')
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN

DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN

SELECT @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList,0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter),LEN(@sInputList))))

-- Indexes to keep the position of searching
IF LEN(@sItem) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0
BEGIN

INSERT INTO @List SELECT @sInputList -- Put the last item in

END

RETURN

END

I think PARSENAME is the neat function to use for this example, as described in this article:

[To see links please register here]

The PARSENAME function is logically designed to parse four-part object names. The nice thing about PARSENAME is that it’s not limited to parsing just SQL Server four-part object names – it will parse any function or string data that is delimited by dots.

The first parameter is the object to parse, and the second is the integer value of the object piece to return. The article is discussing parsing and rotating delimited data - company phone numbers, but it can be used to parse name/surname data also.

Example:

USE COMPANY;
SELECT PARSENAME('Whatever.you.want.parsed',3) AS 'ReturnValue';


The article also describes using a Common Table Expression (CTE) called ‘replaceChars’, to run PARSENAME against the delimiter-replaced values. A CTE is useful for returning a temporary view or result set.

After that, the UNPIVOT function has been used to convert some columns into rows; SUBSTRING and CHARINDEX functions have been used for cleaning up the inconsistencies in the data, and the LAG function (new for SQL Server 2012) has been used in the end, as it allows referencing of previous records.

it is so easy, you can take it by below query:

DECLARE @str NVARCHAR(MAX)='ControlID_05436b78-04ba-9667-fa01-9ff8c1b7c235,3'
SELECT LEFT(@str, CHARINDEX(',',@str)-1),RIGHT(@str,LEN(@str)-(CHARINDEX(',',@str)))

Try this:

declare @csv varchar(100) ='aaa,bb,csda,daass';
set @csv = @csv+',';

with cte as
(
select SUBSTRING(@csv,1,charindex(',',@csv,1)-1) as val, SUBSTRING(@csv,charindex(',',@csv,1)+1,len(@csv)) as rem
UNION ALL
select SUBSTRING(a.rem,1,charindex(',',a.rem,1)-1)as val, SUBSTRING(a.rem,charindex(',',a.rem,1)+1,len(A.rem))
from cte a where LEN(a.rem)>=1
) select val from cte

ALTER FUNCTION [dbo].[StringListTo] (@StringList Nvarchar(max),@Separators char(1),@start int, @index int )
RETURNS nvarchar(max)
AS
BEGIN
declare @out Nvarchar(max)
declare @i int
declare @start_old int
set @start=@start+1
set @i=1
while(@i<=@index)
begin
set @start_old=@start
set @start=CHARINDEX('.',@StringList,@start+1)
if (@start>0)
begin
set @out=Substring(@StringList,@start_old+1,@start-@start_old-1)
end
else
begin
set @out=Substring(@StringList,@start_old+1,len(@StringList)-1)
end
set @i=@i+1
end
RETURN @out
END;

With SQL Server 2016 we can use string_split to accomplish this:

create table commasep (
id int identity(1,1)
,string nvarchar(100) )

insert into commasep (string) values ('John, Adam'), ('test1,test2,test3')

select id, [value] as String from commasep
cross apply string_split(string,',')

Your purpose can be solved using following query -

Select Value , Substring(FullName, 1,Charindex(',', FullName)-1) as Name,
Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as Surname
from Table1

There is no readymade Split function in sql server, so we need to create user defined function.

CREATE FUNCTION Split (
@InputString VARCHAR(8000),
@Delimiter VARCHAR(50)
)

RETURNS @Items TABLE (
Item VARCHAR(8000)
)

AS
BEGIN
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END

IF (@Delimiter IS NULL OR @Delimiter = '')
SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

DECLARE @Item VARCHAR(8000)
DECLARE @ItemList VARCHAR(8000)
DECLARE @DelimIndex INT

SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT INTO @Items VALUES (@Item)

-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE

IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
BEGIN
SET @Item = @ItemList
INSERT INTO @Items VALUES (@Item)
END

-- No delimiters were encountered in @InputString, so just return @InputString
ELSE INSERT INTO @Items VALUES (@InputString)

RETURN

END -- End Function
GO

---- Set Permissions
--GRANT SELECT ON Split TO UserRole1
--GRANT SELECT ON Split TO UserRole2
--GO

select distinct modelFileId,F4.*
from contract
cross apply (select XmlList=convert(xml, '<x>'+replace(modelFileId,';','</x><x>')+'</x>').query('.')) F2
cross apply (select mfid1=XmlNode.value('/x[1]','varchar(512)')
,mfid2=XmlNode.value('/x[2]','varchar(512)')
,mfid3=XmlNode.value('/x[3]','varchar(512)')
,mfid4=XmlNode.value('/x[4]','varchar(512)') from XmlList.nodes('x') F3(XmlNode)) F4
where modelFileId like '%;%'
order by modelFileId

Try this (change instances of ' ' to ',' or whatever delimiter you want to use)

CREATE FUNCTION dbo.Wordparser
(
@multiwordstring VARCHAR(255),
@wordnumber NUMERIC
)
returns VARCHAR(255)
AS
BEGIN
DECLARE @remainingstring VARCHAR(255)
SET @remainingstring=@multiwordstring

DECLARE @numberofwords NUMERIC
SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, ' ', '')) + 1)

DECLARE @word VARCHAR(50)
DECLARE @parsedwords TABLE
(
line NUMERIC IDENTITY(1, 1),
word VARCHAR(255)
)

WHILE @numberofwords > 1
BEGIN
SET @word=LEFT(@remainingstring, CHARINDEX(' ', @remainingstring) - 1)

INSERT INTO @parsedwords(word)
SELECT @word

SET @remainingstring= REPLACE(@remainingstring, Concat(@word, ' '), '')
SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, ' ', '')) + 1)

IF @numberofwords = 1
BREAK

ELSE
CONTINUE
END

IF @numberofwords = 1
SELECT @word = @remainingstring
INSERT INTO @parsedwords(word)
SELECT @word

RETURN
(SELECT word
FROM @parsedwords
WHERE line = @wordnumber)

END

Example usage:

SELECT dbo.Wordparser(COLUMN, 1),
dbo.Wordparser(COLUMN, 2),
dbo.Wordparser(COLUMN, 3)
FROM TABLE

**This worked for me**

CREATE FUNCTION [dbo].[SplitString](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE ( val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records®
RETURN
END