Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- splits a string by a delimiter string and returns each part in a table with a 1-based index in order of occurrence.
- -- it should be deterministic, no?
- IF OBJECT_ID('dbo.ufn_Split',N'TF') IS NOT NULL DROP FUNCTION dbo.ufn_Split;
- GO
- CREATE FUNCTION dbo.ufn_Split( @str nvarchar(4000), @d nvarchar(5) )
- RETURNS @List TABLE (id int PRIMARY KEY, item nvarchar(4000))
- --WITH RETURNS NULL ON NULL INPUT
- AS
- BEGIN
- DECLARE @pos int = 1, @lpos int = 1, @id int = 0
- WHILE LEN(@str) >= @pos BEGIN
- SET @id = @id+1; SET @pos = CHARINDEX(@d,@str,@lpos);
- IF @pos = 0 SET @pos = LEN(@str)+1;
- INSERT INTO @List SELECT @id AS id, SUBSTRING(@str,@lpos,@pos-@lpos);
- SET @lpos = @pos+1;
- END
- RETURN
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement