help splitting a sentence into columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help splitting a sentence into columns

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-03-23 : 14:34:57

How can I split data that has comma delimiters into 4 separate columns?

For example:

Declare @title varchar(255)
Set @title = '1207648,R15754-00 B,Asco,SC.8238.S402 24VDC(Compliant)'

I would like to see the results as:

Column A | Column B | Column C | Column D
1207648 | R15754-00 B | Asco | SC.8238.S402 24VDC (Compliant)

I can't use 'Parsename' because there may periods (dots) used in the data.

Any help or suggestion is greatly appreciated.
Thanks,

Jose

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-23 : 15:41:50

The following code I got from one of the BLOGs and I can't remember from where it is.
[This is not mine - so requesting the owner not to sue me for copy right]

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
While (Charindex ( @SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-23 : 15:56:42

I had some similar requirements, found this function, feel free to use / modify

rockmoose

Declare @title varchar(255)
Set @title = '1207648,R15754-00 B,Asco,SC.8238.S402 24VDC(Compliant)'

select	dbo.fnGetCsvPart(@title,0,default)
	,dbo.fnGetCsvPart(@title,1,default)
	,dbo.fnGetCsvPart(@title,2,default)
	,dbo.fnGetCsvPart(@title,3,default)

select dbo.fnGetCsvPart(@title,2,1) -- "last" will get the rest of the string from the 2nd comma 


/* here is the function, zero-based index */
create  function dbo.fnGetCsvPart(@csv varchar(8000),@index tinyint, @last bit = 0)
returns varchar(4000)
as
begin
	declare @i int; set @i = 0
	while 1 = 1
	begin
		if @index = 0
		begin
			if @last = 1 or charindex(',',@csv,@i+1) = 0
				return substring(@csv,@i+1,len(@csv)-@i+1)
			else
				return substring(@csv,@i+1,charindex(',',@csv,@i+1)-@i-1)
		end
		select @index = @index-1, @i = charindex(',',@csv,@i+1)
 		if @i = 0 break
	end
	return null
end

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 10:32:40

Just to point out that you can use parsename if you really want to...

The other solutions are more generic though, of course

declare @title varchar(255)
set @title = '1207648,R15754-00 B,Asco,SC.8238.S402 24VDC(Compliant)'
set @title = replace(@title, '.', '¬') --or whatever
set @title = replace(@title, ',', '.')
select
    replace(parsename(@title, 4), '¬', '.'),
    replace(parsename(@title, 3), '¬', '.'),
    replace(parsename(@title, 2), '¬', '.'),
    replace(parsename(@title, 1), '¬', '.')

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-03-24 : 10:56:45

Srinika, The code you provided works but does not return the results in multiple columns using
only one row.

Rockmoose & RyanRandall, The code works great and will be using it (Not sure which one yet; is there any advantage to using one over the other?). Thanks for also pointing out that 'Parsename' could still be used by replacing the '.' to another character, then replacing that character back at the end. I did think about doing this but wasn't sure if it would work.

Thanks again guys!

Jose

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-24 : 11:14:40

Thanks Jose

I reckon the parsename way will be faster (that's a guess), but you won't be able to extend it to work with more columns, and you have to use a character you know won't be in the data (which might not be possible).

I'd choose based on those criteria...

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-24 : 12:32:37

quote:
Originally posted by jose1lm

Srinika, The code you provided works but does not return the results in multiple columns using only one row.

U may need to modify the one I proposed, to be used as u want, but other solutions can be directly answering ur Q.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-03-24 : 18:29:59

You can use whichever method you like. Both are ok imo.
Ryan's list of criteria is good.

Srinika, you are not solving the problem Jose had.
He wants to split a csv list into columns, not rows.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources