Solved

SQL Server - string parsing

Posted on 2010-11-15
9
564 Views
Last Modified: 2012-05-10
Hi,

I am passing a string to SQL Server 2005 that I want broken down into constituent words, where the delimiter is a comma.

For example, the input string might be "the, cat, in, the, hat", which I want broken down into the components to be inserted into a table.

Any thoughts on how to do this?
0
Comment
Question by:GroganJ
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
please read this article on how to do the split part.
http://www.experts-exchange.com/A_1536.html

can be easily put with a INSERT ... SELECT ... FROM dbo.ParmsToList( ... ) as needed
0
 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
You have to write function for that.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
This is the funciton I am using.
set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go



--select * from [fn_Split]('str,asf',',')

ALTER function [dbo].[fn_Split](

 @String nvarchar (4000),

 @Delimiter nvarchar (10)

 )

returns @ValueTable table ([Value] nvarchar(4000))

begin

 declare @NextString nvarchar(4000)

 declare @Pos int

 declare @NextPos int

 declare @CommaCheck nvarchar(1)

 

 --Initialize

 set @NextString = ''

 set @CommaCheck = right(@String,1) 

 --print @CommaCheck

 --Check for trailing Comma, if not exists, INSERT

 --if (@CommaCheck <> @Delimiter )

 set @String = @String + @Delimiter

 

 --Get position of first Comma

 set @Pos = charindex(@Delimiter,@String)

 set @NextPos = 1

 

 --Loop while there is still a comma in the String of levels

 while (@pos <>  0)  

 begin

  set @NextString = substring(@String,1,@Pos - 1)

 

  insert into @ValueTable ( [Value]) Values (@NextString)

 

  set @String = substring(@String,@pos +1,len(@String))

  

  set @NextPos = @Pos

  set @pos  = charindex(@Delimiter,@String)

 end

 

 return

end

Open in new window


SELECT * FROM [fn_Split]('the, cat, in, the, hat',',')
will return splited string as a table

Raj
0
 
LVL 6

Accepted Solution

by:
subhashpunia earned 500 total points
Comment Utility
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 6

Expert Comment

by:dan_mason
Comment Utility
The above functions will work, but performance-wise it is much quicker to do it with the help of a Numbers table in your database. For large datasets I've found there's a huge difference in performance. The principle is explained on the following webpage, and the example of what you want to do is under the heading 'parsing a string':

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
0
 

Author Closing Comment

by:GroganJ
Comment Utility
Excellent link - that solved my problem perfectly.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
so, my link didn't help :)

glad we could help !
CHeers
0
 
LVL 3

Expert Comment

by:expert_dharam
Comment Utility
declare @string varchar(100)
set @string = 'the, cat, in, the, hat'

declare @position int
declare @piece varchar(100)

if right(rtrim(@string),1) <> ','
set @string = @string  + ','

set @position =  patindex('%,%' , @string)
while @position <> 0
begin
set @piece = left(@string, @position - 1)
 
print cast(@piece as varchar(100))

set @string = stuff(@string, 1, @position, '')
set @position =  patindex('%,%' , @string)
end
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
I also feel to comment like AngelIII did :)

Rajkumar
http:#34135492
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
get all customers 2 38
SQL Server Select on LT running Totals 6 51
Stored Procedure error 45 39
MS SQL 2005 Srink database in chunks 4 40
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now