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

Posted by Mandlaa under Sql Server on 2/4/2014 | Points: 10 | Views : 14385 | Status : [Member] | Replies : 23
I have a table like this

Id FullName
-------------------
1 Cleo,Smith,james

I want to separate the comma delimited string into two columns

Id FullName Name Surname Lastname
--------------------------------------- --------
1 Cleo,Smith Cleo Smith james

How can i do this?
Give me query for this?




Responses

Posted by: Sravan661 on: 2/4/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/5/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--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

Posted by: Sravan661 on: 2/5/2014 [Member] Bronze | Points: 25

Up
0
Down
Hi
Please revert if you have any questions
Hope it worked

sravan

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

Posted by: Mandlaa on: 2/5/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/5/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 2/5/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sravan661 on: 2/5/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 2/5/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sravan661 on: 2/6/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Sravan661 on: 2/13/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/13/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--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

Posted by: Kshaik on: 2/21/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 2/21/2014 [Member] Starter | Points: 25

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

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

Posted by: Sravan661 on: 2/22/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 2/28/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sravan661 on: 2/28/2014 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 2/28/2014 [Member] Starter | Points: 25

Up
0
Down
@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

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--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

Posted by: Mandlaa on: 2/28/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 3/2/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 3/4/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Mandlaa on: 3/4/2014 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response