Wednesday, 3 September 2014

How to split a string with specific words in sql server

CREATE function [dbo].[SplitByString]
(
@string nvarchar(4000),
@delimiter nvarchar(10),
@addDelimetrContent bit
)
returns @table table
(
[Value] nvarchar(4000)
)
begin
declare @nextString nvarchar(4000)
declare @pos int, @nextPos int

SET @nextString = ''
SET @string = @string + @delimiter

SET @pos = CHARINDEX(@delimiter, @string)
SET @nextPos = 1
WHILE (@pos <> 0)
BEGIN

IF (@addDelimetrContent = 1)
SET @nextString = @delimiter + SUBSTRING(@string, 1, @pos - 1)
ELSE
SET @nextString = SUBSTRING(@string, 1, @pos - 1)

IF (@nextString != '' AND @nextString != @delimiter)
BEGIN
INSERT INTO @table ([Value])
VALUES (@nextString)
END
SET @string = SUBSTRING(@string, @pos + LEN(@delimiter), LEN(@string))
SET @nextPos = @pos
SET @pos = CHARINDEX(@delimiter, @string)
END
RETURN
END

Ex : select * from dbo.[SplitByString]('http://www.google.comhttp://gmail.comhttp://yahoo.com','http',1)

Note : Make Sure that if @addDelimetrContent = 1 then we will add delimeter string to each row otherwise will add as empty.

No comments:

Post a Comment