How to split a comma-separated value to columns in sql server

Hi,

declare @str varchar(100) = 'John,xyz'
Insert into <tablename>
select @str,LEFT(@str,CHARINDEX(',',@str)-1), right(@str,LEN(@str)-CHARINDEX(',',@str))
Go
select * from <tablename>
Go

Hope this helps you

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

--Sample code

declare @var VARCHAR(100) = 'bandi,chandrika'
select @var, Left(@var, CHARINDEX(',', @var)-1) as firstName, STUFF( @var, 1, CHARINDEX(',', @var), '') as lastName

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Please revert if you have any questions
Hope it worked

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Thanks,
declare @str varchar(100) = 'John,xyz'
this is static value
In my above table already data is their,

How can i do?
Can you please give me procedure for this one help me

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

select fullname, Left(fullname, CHARINDEX(',', fullname)-1) as firstName, STUFF( fullname, 1, CHARINDEX(',', fullname), '') as lastName
from tablename

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Thank you,
Suppose I have fullname value is 3 or more than values after what can i do?

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Though you have more than 2 words in the name like cheela, sravan kumar comma will be mentioned only after the last name and no other comma comes, rest of name will be first name

Hope you got me

Please mark as answer if you satisfied . . .

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

That's ok,
I have another column Lastname
How can i do?
cheela,sravan,kumar

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
The following method is generic one use this code

Hope helps you

DECLARE @InputString VARCHAR(MAX)
SET @InputString='Cheela,sravan,kumar'
DECLARE @TOTAL INT;
SELECT @TOTAL=(LEN(@InputString) - LEN(REPLACE(@InputString, ',', '')))
DECLARE @MAINSTRING VARCHAR(1000)  
DECLARE @SUBSTRING VARCHAR(100)  
DECLARE @REMAININGSTRING VARCHAR(1000)  
DECLARE @INTINDEX INT  
SET @MAINSTRING=@InputString
DECLARE @SQL VARCHaR(max)
SET @SQL='SELECT '
DECLARE @I INT;
SET @I=0;
 WHILE @I<@TOTAL
	BEGIN
		IF @I=0
		BEGIN
			SET @INTINDEX=CHARINDEX(',',@MAINSTRING,1)
			SET @SUBSTRING=SUBSTRING(@MAINSTRING,1,@INTINDEX-1)
			SET @REMAININGSTRING=SUBSTRING(@MAINSTRING,@INTINDEX+1,1000)
			SET @SQL =@SQl+''''+ @SUBSTRING +''''+' AS FIRSTNAME,'
			
		END
		ELSE 
		BEGIN
			SET @INTINDEX=CHARINDEX(',',@REMAININGSTRING,1)
			SET @SUBSTRING=SUBSTRING(@REMAININGSTRING,1,@INTINDEX-1)
			SET @REMAININGSTRING=SUBSTRING(@REMAININGSTRING,@INTINDEX+1,1000)
			SET @SQL =@SQl+''''+ @SUBSTRING+'''' +' AS MIDDLENAME ,'
		END
		SET @I=@I+1;
	END
	SET @SQL =@SQl+''''+ @REMAININGSTRING+'''' +' AS LASTNAME'
	
	exec(@SQL)

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
Does it helped you
revert if you have any queries
else
Mark as ANSWER if satisfied

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

--Sample Code with input data

--Dynamic Query
CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test VALUES (1,'This,is,a,test,string')
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns') 

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test)

SELECT 
    @select='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('','',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '','' + data +'','' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '','') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'

EXEC(@select)

DROP TABLE #test

id	col1	col2	col3	col4	col5	col6	col7	col8	col9
1	This	is	a	test	string	NULL	NULL	NULL	NULL
2	See	if	it	can	be	split	into	many	columns

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @test VARCHAR(250)
SET @test=' Cleo,Smith,james'
SELECT @test AS FullName ,
SUBSTRING(@test,1,CHARINDEX(',',@test)-1) AS FirstName ,
LEFT( RTRIM(LTRIM(SUBSTRING(@test,CHARINDEX(',',@test)+1,len(@test)))),
CHARINDEX(',', RTRIM(LTRIM(SUBSTRING(@test,CHARINDEX(',',@test)+1,
LEN(@test)))))-1) AS SurName,
RIGHT(@test,CHARINDEX(',',REVERSE(@test))-1) AS LastName

If it helps/directs you towards the solution,Mark This Response as Answer link

kshaik

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi @ Bandi,
I want Insert data values After divide comma separated values

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
The following method is generic one use this code

Hope helps you


DECLARE @InputString VARCHAR(MAX)
SET @InputString='Cheela,sravan,kumar'
DECLARE @TOTAL INT;
SELECT @TOTAL=(LEN(@InputString) - LEN(REPLACE(@InputString, ',', '')))
DECLARE @MAINSTRING VARCHAR(1000)
DECLARE @SUBSTRING VARCHAR(100)
DECLARE @REMAININGSTRING VARCHAR(1000)
DECLARE @INTINDEX INT
SET @MAINSTRING=@InputString
DECLARE @SQL VARCHaR(max)
SET @SQL='SELECT '
DECLARE @I INT;
SET @I=0;
WHILE @I<@TOTAL
BEGIN
IF @I=0
BEGIN
SET @INTINDEX=CHARINDEX(',',@MAINSTRING,1)
SET @SUBSTRING=SUBSTRING(@MAINSTRING,1,@INTINDEX-1)
SET @REMAININGSTRING=SUBSTRING(@MAINSTRING,@INTINDEX+1,1000)
SET @SQL =@SQl+''''+ @SUBSTRING +''''+' AS FIRSTNAME,'

END
ELSE
BEGIN
SET @INTINDEX=CHARINDEX(',',@REMAININGSTRING,1)
SET @SUBSTRING=SUBSTRING(@REMAININGSTRING,1,@INTINDEX-1)
SET @REMAININGSTRING=SUBSTRING(@REMAININGSTRING,@INTINDEX+1,1000)
SET @SQL =@SQl+''''+ @SUBSTRING+'''' +' AS MIDDLENAME ,'
END
SET @I=@I+1;
END
SET @SQL =@SQl+''''+ @REMAININGSTRING+'''' +' AS LASTNAME'

exec(@SQL)

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

I want splited values Inserted into ActualTable,

Id FullName Name Surname Last
--- ------------ ------- ----------- ------
1 Cleo,SmithJames Cleo Smith james

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Create a function with the above query passing @InputString as parameter (make dynamic)

Use Insert Query

Insert into ActualTableName
db.functionName(@InputString)

Hope this helps you
Revert if you have any issues

Mark as answer if satisfied

sravan

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

@Sravan661

for suppose I have More then 3 values This example not working,
Can you give me the complete example for this one,
I am struggling lot,
plz help me

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

--Dynamic Query 
CREATE TABLE #test(id int, data varchar(100)) 
 
INSERT INTO #test VALUES (1,'This,is,a,test,string') 
INSERT INTO #test VALUES (2,'See,if,it,can,be,split,into,many,columns')  
 
SELECT * FROM #test 
DECLARE @pivot varchar(8000) 
DECLARE @select varchar(8000) 
 
SELECT  
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']' 
FROM  
        master..spt_values where type='p' and  
        number<=(SELECT max(len(data)-len(replace(data,',',''))) FROM #test) 
 
SELECT  
    @select=' select p.*  INTO TempTab
 		        
        from ( 
        select  
            id,substring(data, start+2, endPos-Start-2) as token, 
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n 
        from ( 
            select  
                id, data, n as start, charindex('','',data,n+2) endPos 
                from (select number as n from master..spt_values where type=''p'') num 
                cross join  
                ( 
                    select  
                        id, '','' + data +'','' as data  
                    from  
                        #test 
                ) m 
            where n < len(data)-1 
            and substring(data,n+1,1) = '','') as data 
        ) pvt 
        Pivot ( max(token)for n in ('+@pivot+'))p' 
 
--PRINT (@select) 
EXEC (@Select )

--Select data from TempTab
	IF OBJECT_ID('dbo.TempTab') IS NOT NULL
	BEGIN
		SELECT * FROM TempTab
		DROP TABLE TempTab
	END
 DROP TABLE #test 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hi @Bandi,
It's working fin,
After running the procedure working fin,
After that again Select SELECT * FROM #test

It display actual data only,Not display comma separated values data,
That's why i am asking Comma separated values also insert into actual table.
Id Col Col1 Col2 col3
1 See if it Null

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Hello,

The below part is for selecting given data... ( TempTab)
Try to look into the query so that you will get the changes atleast...

IF OBJECT_ID('dbo.TempTab') IS NOT NULL
BEGIN
SELECT * FROM TempTab
DROP TABLE TempTab -- Here am dropping newly created table..... Instead of dropping you can make use of this table (TempTab) with those columns .After that drop this table...
END

NOTE:
You just execute complete query (earlier posted query) once in your SSMS. Then tweet your query as per above posted query

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Mark This Response as Answer link if the above solution helped you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

I a doing this one getting an error like this,
ERROR:Reference to database and/or server name in 'master..spt_values' is not supported in this version of SQL Server in AZURE.

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

USE [stgdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

SET ARITHABORT ON
GO
CREATE TABLE [dbo].[Productsdata1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TagId]  AS ('100.1.'+CONVERT([varchar](10),[ID],0)) PERSISTED NOT NULL,	
	[VinNo] [nvarchar](max) NULL,	
	[Data] [nvarchar](max) NULL,
        [Col] [nvarchar](max) NULL,
        [Col] [nvarchar](max) NULL,
        [Col] [nvarchar](max) NULL,
 CONSTRAINT [PK__Student___657CF9AC47D80312] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[TagId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
GO

SET ANSI_PADDING OFF
GO

Actually this is my table,
In column(Data) value Split comma separated value and stored in table,
This is the My table structured,Can you give me Solution for this one,
Give me Complete Stored Procedure plz,

I am Trying this one getting an error like this
ERROR:Reference to database and/or server name in 'master..spt_values' is not supported in this version of SQL Server.

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator