Multi separators to split the string


Every application has some database requirement where Application developer or Database developer or DBAs has to split the string in different ways as per the requirements. In this article I am sharing some string splitter functions to work on it.

Workaround
Input = Input String, Criteria separator, Conditional separator
Output = Returns the table which contains separated string for given inputs

CREATE FUNCTION [dbo].[udf_utlGetSplittedSearchCriterias]

(

      @varInputString VARCHAR(4000),

      @varCriteriaSeperator VARCHAR(1),

      @varConditionSeperator VARCHAR(1)

)

RETURNS @tblSearchCriterias TABLE ( AttributeName VARCHAR(100), AttributeValue VARCHAR(1000) )

AS

BEGIN

      WHILE @varInputString != ''

      BEGIN

            INSERT      @tblSearchCriterias

            SELECT      RTRIM(

                              LTRIM(

                                          SUBSTRING(

                                                            SUBSTRING(  @varInputString, 1,

                                                                              CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                              THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                              ELSE LEN( @varInputString )

                                                                              END), 1,

                                                                              CASE WHEN CHARINDEX( @varConditionSeperator,

                                                                                    SUBSTRING(  @varInputString, 1,

                                                                                                      CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                                                      THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                                                      ELSE LEN( @varInputString )

                                                                                                      END)

                                                                              ) = 0 THEN 0

                                                                              ELSE CHARINDEX( @varConditionSeperator,

                                                                                    SUBSTRING( @varInputString, 1,

                                                                                                      CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                                                      THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                                                      ELSE LEN( @varInputString )

                                                                                                      END)

                                                                              ) - 1

                                          END)

                              )

                        ) AS AttributeName,

                        RTRIM(

                              LTRIM(

                                    SUBSTRING(

                                                SUBSTRING(  @varInputString, 1,

                                                                  CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                  THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                  ELSE LEN( @varInputString )

                                                                  END),

                                                                  CHARINDEX(      @varConditionSeperator,

                                                                                    SUBSTRING( @varInputString, 1,

                                                                                                      CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                                                      THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                                                      ELSE LEN( @varInputString )

                                                                                                      END)

                                                                                    )+ 1 , LEN(SUBSTRING(    @varInputString, 1,

                                                                                                                        CASE WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                                                                                        THEN CHARINDEX( @varCriteriaSeperator, @varInputString ) - 1

                                                                                                                        ELSE LEN( @varInputString )

                                                                                                                        END)

                                                                                    )

                                                )

                                    )

                        ) AS AttributeValue

            SET @varInputString = CASE    WHEN CHARINDEX( @varCriteriaSeperator, @varInputString ) > 0

                                                      THEN SUBSTRING( @varInputString, CHARINDEX( @varCriteriaSeperator, @varInputString ) +1 , LEN(@varInputString ))

                                                      ELSE ''

                                            END

END

      RETURN

END
GO
Example = SELECT * FROM [udf_utlGetSplittedSearchCriterias]('Example = Microsoft SQl Server, Keyword = T-SQL',  ',', '=')
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s