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.


Related Articles

How to get rows into comma separated values column in sql

How to get rows into comma separated values column in sql. Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution. Use this to get all row values into one single column value with comma separation.

Split Comma separated values into Columns

In this article, I explain about how to split a string with delimiter into list of rows. De limiter can be comma or plus our requirement is to convert a single row of a table into multiple rows based on number of delimiter.

More articles: Comma separated value

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: