How to split a comma-separated value to columns

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  Surnamefrom 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))ASBEGIN      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)      RETURNEND -- End FunctionGO---- Set Permissions--GRANT SELECT ON Split TO UserRole1--GRANT SELECT ON Split TO UserRole2--GO

;WITH Split_Names (Value,Name, xmlname)AS(    SELECT Value,    Name,    CONVERT(XML,'<Names><name>'      + REPLACE(Name,',', '</name><name>') + '</name></Names>') AS xmlname      FROM tblnames) SELECT Value,       xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name,     xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname FROM Split_Names

and also check the link below for reference

http://jahaines.blogspot.in/2009/06/converting-delimited-string-of-values.html

xml based answer is simple and clean

refer this

DECLARE @S varchar(max),        @Split char(1),        @X xmlSELECT @S = 'ab,cd,ef,gh,ij',       @Split = ','SELECT @X = CONVERT(xml,' <root> <myvalue>' +REPLACE(@S,@Split,'</myvalue> <myvalue>') + '</myvalue>   </root> ')SELECT  T.c.value('.','varchar(20)'),              --retrieve ALL values at once  T.c.value('(/root/myvalue)[1]','VARCHAR(20)')  , --retrieve index 1 only, which is the 'ab'  T.c.value('(/root/myvalue)[2]','VARCHAR(20)') FROM @X.nodes('/root/myvalue') T(c)