Nov 6, 2015

Get index of occurrence any characters from string in SQL Server

Return index of the first occurrence in the string of any character in a specified string characters.

CREATE FUNCTION [dbo].[IndexOfAny] (
      @Chars VARCHAR(100),
      @Delimiter VARCHAR(10),
      @String VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
      DECLARE @Index AS INT
      DECLARE @Length AS INT
      DECLARE @Value AS VARCHAR(100)

      SET @Length = 0

      WHILE @Length >= 0
      BEGIN
            SET @Length = CHARINDEX(@Delimiter, @Chars) - 1

            IF @Length > 0
            BEGIN
                  SET @Value = LEFT(@Chars, @Length)
                  SET @Chars = STUFF(@Chars, 1, @Length + LEN(@Delimiter), '')
            END
            ELSE
                  SET @Value = @Chars

            SET @Index = CASE
                        WHEN CHARINDEX(@Value, @String) <> 0
                              AND (
                                    @Index IS NULL
                                    OR CHARINDEX(@Value, @String) < @Index
                                    )
                              THEN CHARINDEX(@Value, @String)
                        ELSE @Index
                        END
      END

      RETURN ISNULL(@Index, 0)
END


Example:
SELECT [dbo].[IndexOfAny]('A,B,C', ',', 'xxxBxxxAxxxCxxx')


Output: 4

No comments:

Post a Comment