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.
