sql function to return a table from comma separated value

It's very important function becuase many times you want to convert a csv string into a temp table and then join it in your regular query

for example you have a string of Order IDs as below and you want to have it in temp table


decalare @OrdIDs varchar(1000)
set OrdIDs = '1,2,3,4,5,6,7,8,9'



Now you want to have a function which gets this string and returns a table with all values being populated in row so that you can use that table in sql join

here you go


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: HITESH PANDYA
-- Description: CONVERTS CSV OF INTEGER TO A TABLE DATA
-- =============================================
CREATE FUNCTION [dbo].[UFT_INTCSVTOTABLE]
(

@INPUTCSV NVARCHAR(MAX)

)
RETURNS @TBL TABLE
(
COL1 INT
)
AS
BEGIN

DECLARE @NUM_STR NVARCHAR(MAX),
@@SUBSTR NVARCHAR(100),
@IDX INT,
@LENTH INT

SET @NUM_STR = @INPUTCSV

--PRINT LEN(@NUM_STR)
SET @NUM_STR = REPLACE(@NUM_STR,' ','')
--PRINT @NUM_STR

SET @LENTH = LEN(@NUM_STR)

WHILE (CHARINDEX(',',@NUM_STR) > 0 AND @LENTH > 0)
BEGIN

SET @IDX = CHARINDEX(',', @NUM_STR)
SET @@SUBSTR = SUBSTRING(@NUM_STR, 0, @IDX)

--PRINT @@SUBSTR
IF LTRIM(RTRIM(@@SUBSTR)) <> ''
INSERT INTO @TBL VALUES(LTRIM(RTRIM(@@SUBSTR)))

--PRINT 'INDEX: ' + CONVERT(NVARCHAR,@IDX) + ' SUBSTR: ' + @@SUBSTR + ' NUMSTR: ' + @NUM_STR
SET @NUM_STR = SUBSTRING(@NUM_STR, @IDX + 1, @LENTH - @IDX)

SET @LENTH = LEN(@NUM_STR)
END

-- PRINT 'LAST: ' + @NUM_STR

IF LTRIM(RTRIM(@NUM_STR)) <> ''
INSERT INTO @TBL VALUES(LTRIM(RTRIM(@NUM_STR)))

--SELECT * FROM @TBL



RETURN


this is how you can use this function in simple select query



SELECT * FROM UDF_INTCSVTOTABLE(@OrdIDs)




You can write a similar one for more columns and datatypes.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: