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.

Comments

Popular posts from this blog

List of special Characters in HTML

How to Read TOC Headings from word document using C#