CREATE FUNCTION [dbo].[SplitedItemAt]
(
@elements nvarchar(max),
@separator nvarchar(max),
@position int
)
RETURNS xml
AS
BEGIN
--insert into @Result
DECLARE @Result nvarchar(max) = ''
-- Add the T-SQL statements to compute the return value here
declare @start_location int, @curr_location int, @count int, @separator_length int
select @start_location = 1, @count = 0, @separator_length = len(@separator)
while @start_location!=0
begin
set @count = @count + 1
select @curr_location = charindex(@separator, @elements, @start_location)
if (@curr_location=0)
begin
set @start_location = 0
end
else
begin
set @start_location = @curr_location+@separator_length
end
if(@count=@position)
begin
select @Result = substring(@elements, @start_location, @curr_location-@start_location)
break
end
end
-- Return the result of the function
RETURN @Result
END
GO
CREATE FUNCTION [dbo].[SplitedItemAt] ( @elements nvarchar(max), @separator nvarchar(max), @position int ) RETURNS nvarchar(max) AS BEGIN --insert into @Result DECLARE @Result nvarchar(max) = '' -- Add the T-SQL statements to compute the return value here declare @start_location int, @curr_location int, @count int, @separator_length int select @start_location = 1, @count = 0, @separator_length = len(@separator), @curr_location = 0 while @curr_location<len(@elements) begin set @count = @count + 1 select @curr_location = charindex(@separator, @elements, @start_location) if (@curr_location=0) begin select @curr_location = len(@elements)+1 end if(@count=@position) begin select @Result = substring(@elements, @start_location, @curr_location-@start_location) break end set @start_location = @curr_location+@separator_length end -- Return the result of the function RETURN @Result END GO select dbo.[SplitedItemAt]('asdf+ert', '+', 2)