Thursday, September 6, 2007

SQL Split Function

This SQL Split Function is use to SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.

Below is Split Function in SQL
DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END


Result
- SQL
- TUTORIALS

* Copy blue color Sql split function with change @String variable to test the result in your query analyzer.

SQL Split Function

18 comments:

yogesh.narayanan said...

Good one.
This link gave me readymade sql function...
Split Function in SQL Server 2005

ever4lv said...

really good one

vijay priya said...

Thanks for the Code!

Ambition IT said...

Good code. You do not need to declare the @NextPos variable since you do not use it.

cemakpolat said...

I am trying to understand all sql codes.I have executed your code,but I have some doubts.That I dont understand why you use a while looping.According to me after the paramaters will be defined the following codes are sufficient.

SET @NextString = substring(@String,1,@Pos - 1)
PRINT @NextString
SET @String = substring(@String,@pos+1,len(@String)-1)
PRINT @String

Chamila said...

Might be this one also helpful:
http://chamilaw.blogspot.com/2011/05/concatenation-in-select-and-split.html

Raj said...

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello

Raj said...

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello

chimeco said...

Gracias amigo, me sirvio como no tienes indea!!!

LarryB said...
This comment has been removed by the author.
LarryB said...

If the search string contains leading blanks then the algorithm will fail. You must use LTRIM to eliminate the leading blanks prior to starting the split function.

jignesh Panchal said...

create FUNCTION dbo.XSplitString
(@String varchar(8000), @Delimiter char(1)
)
returns @temptable TABLE (Part varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Part) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return

end

M Naseer said...

Good job, saved me a lot of time and also made my logic very smooth, thanks for sharing

M Naseer said...

Good job, saved me a lot of time and also made my logic very smooth, thanks for sharing

Antonio said...

Does it work if there are more than one occurrences of the delimeter in the String? Thanks

Antonio said...

Does it work if there are more than one occurrences of the delimeter in the String? Thanks

Sudhir DBAKings said...

Nice post very helpful

dbakings

Khanh Pham said...

Thanks.

kp