UDF for Seperation of CSV in SQL

Hi All,

This is the UDF for values having delimiter as COMMA(,)

How to Use::

1) Copy & Paste the below UDF in SQL and Execute it

ALTER FUNCTION [dbo].[UF_CSVToTable]
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END

2) Now write the Query eg:

select * from [Your Table Name] where [Your Column Name] IN
                                                                            (Select * from UF_CSVToTable('1,2,3,4,5')

Regards,
Krunal Panchal

Insertion & Selection in SQL storedprocedure with CSV

 Hi All,

This is the Stored Procedure for inserting multiple values in multiple columns in SQL

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertProductMembers]
      (
            @MemberId varchar(100),
            @MemberProductId varchar(100)
      )
AS

      DECLARE @count int
      DECLARE @str VARCHAR(8000)
      DECLARE @spot SMALLINT
       DECLARE @str1 VARCHAR(8000)
      DECLARE @spot1 SMALLINT

    WHILE @MemberProductId <> ''
    BEGIN
        SET @spot = CHARINDEX(',', @MemberProductId)
        IF @spot>0
            BEGIN
                SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS varchar)
                SET @MemberProductId = RIGHT(@MemberProductId, LEN(@MemberProductId)-@spot)
            END
        ELSE
            BEGIN
                SET @str = CAST(@MemberProductId AS varchar)
                SET @MemberProductId = ''
            END
          
      
              

        SET @spot1 = CHARINDEX(',', @MemberId)
        IF @spot1>0
            BEGIN
                SET @str1 = CAST(LEFT(@MemberId, @spot-1) AS varchar)
                SET @MemberId = RIGHT(@MemberId, LEN(@MemberId)-@spot)
            END
        ELSE
            BEGIN
                SET @str1 = CAST(@MemberId AS varchar)
                SET @MemberId = ''
            END        

  
SELECT @count=count(1) FROM test

     WHERE id=@str

     AND id1=@MemberId
  

     IF @count =0

     BEGIN

      INSERT INTO test (id, id1) VALUES( @str1,@str)

     END

   end
  

      RETURN


Regards,
Krunal Panchal