Script to split a comma seperated string to table

,

Requirement:
Multiple Customers were selected from a interface created in VB. A stored procedure was created which accepts the customer codes as a parameter in the form of 100,200,300,400. Wanted to fetch the information for only those customers selected. Ideally we will have to create a dynamic SQL which would be somewhat like this
Set @StrSQL =
‘Select * from Customers where CustomerCode IN (‘
+ @CustomerCode + ‘)’
I wanted to do the same without the use of dynamic SQL.
Solution:
I have tried to find inbuilt procedure/function which splits a delimited string into table.
If u pass a string as '100,200,300,400' then it returns a table having 2 columns as DataValue and ValueAt. The DataValue column contains the values and the ValueAt column will contain the position of the value in the string.
Select *
from DBO.JUF_SplitStringToTable('100,200,300,400, ',')
The result for the above query would be as
DataValue ValueAt
100 1
200 2
300 3
400 4
Now my above query without Dynamic SQL would be as
Eg.: Select * from Customers where CustomerCode IN
(Select DataValue
from DBO.JUF_SplitStringToTable('100,200,300,400, ',')
)

Create  FUNCTION JUF_SplitStringToTable
(	@StringToSplit 	VARCHAR(500),
	@Delimiter 	CHAR(1)
)
/*Author : Renu Mehta*/

RETURNS @SplitTable Table 
(	DataValue	VARCHAR(50),
	ValueAt		Numeric(9,0) IDENTITY(1,1)
)
AS 
BEGIN
Declare @Value VARCHAR(500)
declare @End integer
declare @start integer

  IF len(@StringToSplit) = 0 
     RETURN
	SET @Delimiter = LTRIM(RTRIM(@Delimiter))
	SET @start = 1
	SET @End = 
           charindex(@Delimiter, @stringToSplit, @Start )
  IF @End = 0 
  BEGIN
   SET @Value = SubString(@stringToSplit, @Start, 
        LEN(@stringToSplit))

     Insert into @SplitTable 			
          Select @Value
   END
  Else
  BEGIN
  While @End >= 0
  BEGIN	
    SET @Value = SubString(@stringToSplit, @Start, 
        abs(@End - @Start) )
    Insert into @SplitTable 
	 Select @Value 
    IF @End = 0 
	BREAK

    SET @Start = @End + 1
    SET @End = 
        charindex(@Delimiter, @stringToSplit, @Start)
  END
 END
 RETURN 
END

Rate

Share

Share

Rate