CSV column split - SQL Server Forums

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

SQL Server Development (2000)

CSV column split

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-22 : 08:57:54

Miky writes "Hi, I am using SQL2k
I went through your FAQs, suggested solutions, etc. But was kind of confused on building a SELECT statement for the following:

I have a field with 5 '.' (dot) delimited values. The last value in this column may be absent sometimes. The data looks like

Tom.Springfield.Illinois.Male.A
Karen.Burke.Virginia.Female
Cynthia.Islandia.NY.Female.P

My result should be 5 columns as:

Name State City Gender Status
Tom Springfield Illinois Male A
Karen Burke Virginia Female NULL
Cynthia Islandia NY Female P

(NOTICE THE ABSENT VALUE IS REPLACED BY A 'NULL')

I tried to work my way by using SUBSTRING and a nested CHARINDEX, but could not make it. Would really appreciate any help. Thanks in advance

Miky"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 09:04:25

this is just perfect for our favotite function:


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

you'll probably have to adjust the function to add nulls if there are only 4 values

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 09:15:39

[code]
Declare @myTable table (columnStr varchar(1000), p1 int, p2 int, p3 int, p4 int)
Insert Into @myTable (columnStr)
Select 'Tom.Springfield.Illinois.Male.A'
Union Select 'Karen.Burke.Virginia.Female'
Union Select 'Cynthia.Islandia.NY.Female.P'

Declare @p1 int,
@p2 int,
@p3 int

Update @myTable
Set
@p1 = charindex('.',columnStr),
p1 = @p1,
@p2 = charindex('.',columnStr,@p1+1),
p2 = @p2,
@p3 = charindex('.',columnStr,@p2+1),
p3 = @p3,
p4 = charindex('.',columnStr,@p3+1)
From @myTable

Select
Name = Left(columnStr,p1-1),
State = Substring(columnStr,p1+1,p2-p1-1),
City = Substring(columnStr,p2+1,p3-p2-1),
Gender = case when p4=0 then Substring(columnStr,p3+1,len(columnStr)-p3) else Substring(columnStr,p3+1,p4-p3-1) end,
Status = case when p4=0 then null else Substring(columnStr,p4+1,len(columnStr)-p4) end
From @myTable
[/code]

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources