Convert Delimited String To Table

If you have a delimited string (usually comma-delimited) in a SQL-based application, sometimes it would be easier to process if it’s converted into table. In this post, I will show you a user-defined table-valued function in SQL that I developed for the purpose mentioned before. The function uses three parameters: the delimited string itself, the delimiter, and sorting order (‘A’ for ascending, ‘D’ for descending, and anything for original).

Here’s the code:

CREATE FUNCTION [dbo].[FT_Delimited_String_To_Table]
(
@CDELIMITED_STRING VARCHAR(MAX),
@CDELIMITER CHAR(1),
@CSORT_BY CHAR(1)
)
RETURNS @EntriesTable TABLE
(IENTRY_NO INT IDENTITY(1,1), CENTRY VARCHAR(MAX))
WITH ENCRYPTION
AS
BEGIN
DECLARE @CENTRY AS VARCHAR(MAX),
@IDELIMITER_INDEX AS INT,
@IDELIMITER_START AS INT
DECLARE @TempEntries AS TABLE (
CENTRY VARCHAR(MAX)
)

SELECT @IDELIMITER_INDEX = 1,
@IDELIMITER_START = 1

WHILE @IDELIMITER_INDEX <= LEN(@CDELIMITED_STRING) BEGIN
SET @IDELIMITER_INDEX = CHARINDEX(@CDELIMITER, @CDELIMITED_STRING, @IDELIMITER_INDEX)
IF @IDELIMITER_INDEX = 0 BEGIN
SELECT @IDELIMITER_INDEX = LEN(@CDELIMITED_STRING) + 1
END
SET @CENTRY = SUBSTRING(@CDELIMITED_STRING, @IDELIMITER_START, @IDELIMITER_INDEX - @IDELIMITER_START)
INSERT INTO @TempEntries VALUES (LTRIM(RTRIM(@CENTRY)))
SELECT @IDELIMITER_INDEX = @IDELIMITER_INDEX + 1
SELECT @IDELIMITER_START = @IDELIMITER_INDEX
END

IF @CSORT_BY = 'A' BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries ORDER BY CENTRY ASC
END
ELSE BEGIN
IF @CSORT_BY = 'D' BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries ORDER BY CENTRY DESC
END
ELSE BEGIN
INSERT INTO @EntriesTable
SELECT CENTRY FROM @TempEntries
END
END


RETURN
END

Now try it with this line:

SELECT * FROM FT_Delimited_String_To_Table('Apple, Cherry, Orange, Blackberry', ',', 'A')

Leave a Reply