CREATE FUNCTION [dbo].[fn_String_SplitByDelimiter] ( @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 , @ld INT; set @ll = LEN(@List) + 1 set @ld = 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; ENDExample
SELECT [dbo].fn_String_SplitByDelimiter ( '1ST\2ND\3RD\4TH' ,'\' ,3)
Will output
3RD
taken from the below url, just updated it to work on SQL2005
http://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string
No comments:
Post a Comment