September 2006 - Posts

09-26-2006 1:58 PM

Yet Another SQL Split Function



Ever had a problem with passing multiple values, let's say a set of values from the checked items of a CheckBoxList, to a single sql parameter? The most common approach to this is creating a dynamic sql statement and by looping through the values and creating mutiple "OR" where clauses (ex. "YourField = 'a' OR YourField = 'b'") and appending it to your query statement string and executing it with sp_executesql within your STORED PROCEDURE.

The only problem with the sp_executesql approach is that it can't be invoked within a USER DEFINED FUNCTION. This is the reason why I have created the Split function for SQL. With this function you can simply pass your values as a character delimited string (ex. "a,b,c") to a parameter, then create your query in a STORED PROCEDURE or USER DEFINED FUNCTION, and split the parameter in your where clause.


Basically, the function is similar to VB's split function.


In VB you can split a character delimited string by :

Dim cInputString AS String = "a,b,c,d"
Dim cArrayString() AS String
Dim cMyString AS String
Dim nReturnIndex AS Integer
Dim cDelimiter AS String

cDelimiter = ","

cArrayString = Split(cInputString,cDelimiter) 'returns all the values as elements of an array string

nReturnIndex = 2
cMyString = cArrayString(nReturnIndex) 'returns the value c
cMyString = Split(cInputString,cDelimiter)(nReturnIndex) 'returns the value c



With this SQL Split function, you can do this by :

DECLARE @cInputString AS varchar(10)
DECLARE @cMyString AS varchar(10)
DECLARE @cMyDefaultVal AS varchar(10)
DECLARE @nReturnIndex AS int

SET @cInputString = 'a,b,c,'
SET @cMyDefaultVal = 'd' -- empty string values will be replace with this value
SET @nReturnIndex = NULL -- set return index to null if you want to retrieve all the values

SELECT  Indx
        , CSV
FROM    dbo.Split(cInputString,',',@cMyDefaultVal,@nReturnIndex) -- returns all the values as rows

SELECT  *
FROM    YourTable   
WHERE   YourStringField IN (
        SELECT  CSV  
        FROM    dbo.Split(cInputString,',',@cMyDefaultVal,@nReturnIndex)
    ) OR YourIntField IN (
        SELECT  CONVERT(int,CSV) -- you can convert the CSV value to its proper value
        FROM    dbo.Split('1,2,3,4',',',NULL,NULL)
    )

SET @cMyDefaultVal = NULL -- default value for empty strings can be null
SET @nReturnIndex = 2 -- retrieve an element, index is 0-based

SELECT  @cMyString = CSV
FROM    dbo.Split(cInputString,',',@cMyDefaultVal,@nReturnIndex) -- returns the value c




Here's the code for the Split Function
-----------------------------

CREATE  FUNCTION [dbo].[Split]
(    @cCSV     varchar(8000)
    , @cDelimiter     varchar(5)
    , @cNullEmpty    varchar(8000) = NULL
    , @nGetIndex int = NULL
)
 
RETURNS @Column TABLE
(    Indx    int
    , CSV     varchar(8000)
)

AS

BEGIN

DECLARE @nIndex int
DECLARE    @cValue varchar(8000)
DECLARE @nIndexCounter int

SET @nIndexCounter = 0

SET @nIndex = CHARINDEX(@cDelimiter,@cCSV,0)

IF @nIndex = 0   
    BEGIN
        SET @cValue = @cCSV
   
        IF @cValue = '' AND @cNullEmpty IS NULL
            BEGIN
                SET @cValue = NULL
            END
       
        IF @cValue = '' AND @cNullEmpty IS NOT NULL
            BEGIN
                SET @cValue = @cNullEmpty
            END
       
        IF @nGetIndex = @nIndexCounter OR @nGetIndex IS NULL
            BEGIN
                INSERT INTO
                    @Column
                SELECT    @nIndex
                    , @cValue
            END
                       
        SET @nIndexCounter = @nIndexCounter + 1
       
        SET @cCSV = ''
    END

WHILE LEN(@cCSV) > 0
    BEGIN
        SET @cValue = LEFT(@cCSV,@nIndex - 1)

        IF @cValue = '' AND @cNullEmpty IS NULL
            BEGIN
                SET @cValue = NULL
            END
       
        IF @cValue = '' AND @cNullEmpty IS NOT NULL
            BEGIN
                SET @cValue = @cNullEmpty
            END

        IF @nGetIndex = @nIndexCounter OR @nGetIndex IS NULL
            BEGIN
                INSERT
                INTO    @Column
                SELECT    @nIndexCounter
                    , @cValue
            END

        SET @nIndexCounter = @nIndexCounter + 1
       
        SET @cCSV = RIGHT(@cCSV,LEN(@cCSV) - @nIndex)
       
        SET @nIndex = CHARINDEX(@cDelimiter,@cCSV,0)

        IF @nIndex = 0   
            BEGIN
                SET @cValue = @cCSV

                IF @cValue = '' AND @cNullEmpty IS NULL
                    BEGIN
                        SET @cValue = NULL
                    END
               
                IF @cValue = '' AND @cNullEmpty IS NOT NULL
                    BEGIN
                        SET @cValue = @cNullEmpty
                    END
               
                IF @nGetIndex = @nIndexCounter OR @nGetIndex IS NULL
                    BEGIN
                        INSERT
                        INTO    @Column
                        SELECT    @nIndexCounter
                            , @cValue
                    END
                       
                SET @nIndexCounter = @nIndexCounter + 1
               
                SET @cCSV = ''
            END
    END

RETURN

END
Posted by rhamille with no comments
Filed under: