• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

SQL Server - string parsing

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
GroganJ
Asked:
GroganJ
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Alpesh PatelAssistant ConsultantCommented:
You have to write function for that.
0
 
Rajkumar GsSoftware EngineerCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
subhashpuniaCommented:
0
 
dan_masonCommented:
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
 
GroganJAuthor Commented:
Excellent link - that solved my problem perfectly.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, my link didn't help :)

glad we could help !
CHeers
0
 
expert_dharamCommented:
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
 
Rajkumar GsSoftware EngineerCommented:
I also feel to comment like AngelIII did :)

Rajkumar
http:#34135492
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now