Function To Get Tabular Data From a Delimilted String Variable

Lakhangarg
Posted by Lakhangarg under Sql Server category on | Views : 1800
In this there are two input variables. First is delimited string variable and the Second is delimited character.

CREATE FUNCTION [dbo].[function_string_to_table]
(
@string VARCHAR(7999),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data VARCHAR(256)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
VALUES(LTRIM(RTRIM(SUBSTRING(@string, @start, @end - @start))))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN

END


Suppose we have string variable like:'1,2,3,4,5'
and in this the delimiter character is comma ','

if we call this as:
SELECT [dbo].[function_string_to_table]('1,2,3,4,5',',')

Output of this will be :

1
2
3
4
5

Comments or Responses

Login to post response