Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE FUNCTION dbo.GetSplitString_CTE
- (
- @List VARCHAR(MAX),
- @Delimiter VARCHAR(255),
- @ElementNumber int
- )
- RETURNS VARCHAR(4000)
- AS
- BEGIN
- DECLARE @result varchar(4000)
- DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
- Item VARCHAR(4000)
- )
- DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);
- WITH a AS
- (
- SELECT
- [start] = 1,
- [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
- @List, @ld), 0), @ll),
- [value] = SUBSTRING(@List, 1,
- COALESCE(NULLIF(CHARINDEX(@Delimiter,
- @List, @ld), 0), @ll) - 1)
- UNION ALL
- SELECT
- [start] = CONVERT(INT, [end]) + @ld,
- [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
- @List, [end] + @ld), 0), @ll),
- [value] = SUBSTRING(@List, [end] + @ld,
- COALESCE(NULLIF(CHARINDEX(@Delimiter,
- @List, [end] + @ld), 0), @ll)-[end]-@ld)
- FROM a
- WHERE [end] < @ll
- )
- INSERT @Items SELECT [value]
- FROM a
- WHERE LEN([value]) > 0
- OPTION (MAXRECURSION 0);
- SELECT @result=Item
- FROM @Items
- WHERE position=@ElementNumber
- RETURN @result;
- END
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement