Wednesday, July 28, 2010

Function to Split Multi-valued String

  1. Can you write a query to split a comma seperated value?
  2. Can you create a function to spilt a delimitted string into multipled rows? Delimiter can be any char like comma (,), @, &, ; etc.
  3. How to use a multi valued parameter in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without splitting multiple values.

To find the answer of above questions create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split comma seperated values
--------------------------------------------
Use this function to split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'') + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
*/

No comments:

Post a Comment