How to use Charindex for one or the other character

Mastering CHARINDEX for Multiple Characters in SQL Server

Learn how to effectively use the CHARINDEX function in SQL Server to locate the position of one of several specified characters within a string, enhancing your string manipulation capabilities.

The CHARINDEX function in SQL Server is a powerful tool for finding the starting position of a substring within a string. While its basic usage is straightforward for a single character or substring, scenarios often arise where you need to find the position of any of several possible characters. This article will guide you through various techniques to achieve this, from simple OR conditions to more advanced methods, helping you write more flexible and efficient SQL queries.

Understanding CHARINDEX Basics

Before diving into multi-character searches, let's quickly review the fundamental syntax of CHARINDEX. It returns the starting position of the first occurrence of substring within expression. If substring is not found, it returns 0.

SELECT CHARINDEX('o', 'Hello World'); -- Returns 5
SELECT CHARINDEX('x', 'Hello World'); -- Returns 0

Basic usage of CHARINDEX

Searching for One of Multiple Characters

When you need to find the position of the first occurrence of any character from a predefined set, CHARINDEX alone isn't enough. You need to combine it with other SQL constructs. The goal is to get the position of the character that appears earliest in the string from your list of target characters.

Method 1: Using Multiple CHARINDEX Calls with MIN

The most common and often clearest way to find the earliest occurrence of one of several characters is to call CHARINDEX for each character individually and then use the MIN function to find the smallest non-zero result. This approach is explicit and easy to understand.

DECLARE @MyString VARCHAR(100) = 'This is a test string.';

SELECT
    CASE
        WHEN CHARINDEX('i', @MyString) > 0 AND CHARINDEX('s', @MyString) > 0
        THEN MIN(CHARINDEX('i', @MyString), CHARINDEX('s', @MyString))
        WHEN CHARINDEX('i', @MyString) > 0 THEN CHARINDEX('i', @MyString)
        WHEN CHARINDEX('s', @MyString) > 0 THEN CHARINDEX('s', @MyString)
        ELSE 0
    END AS FirstOccurrencePosition;

-- A more robust way using a subquery or CTE for multiple characters:
WITH CharPositions AS (
    SELECT
        CHARINDEX('i', @MyString) AS Pos_i,
        CHARINDEX('s', @MyString) AS Pos_s,
        CHARINDEX('t', @MyString) AS Pos_t
)
SELECT
    (SELECT MIN(val) FROM (VALUES (Pos_i), (Pos_s), (Pos_t)) AS T(val) WHERE val > 0)
FROM CharPositions;

Finding the minimum position using multiple CHARINDEX calls and MIN

Method 2: Using PATINDEX with Character Sets

For more complex pattern matching, including searching for any character from a set, PATINDEX is a powerful alternative. PATINDEX returns the starting position of the first occurrence of a pattern in an expression, and it supports wildcard characters, including character sets defined with square brackets [].

DECLARE @MyString VARCHAR(100) = 'This is a test string.';

-- Find the first occurrence of 'i', 's', or 't'
SELECT PATINDEX('%[ist]%', @MyString) AS FirstOccurrencePosition;

-- Example with a different string
DECLARE @AnotherString VARCHAR(100) = 'SQL Server Database';
SELECT PATINDEX('%[aeiou]%', @AnotherString) AS FirstVowelPosition;

Using PATINDEX with character sets to find one of multiple characters

PATINDEX Logic for Multiple Characters

Method 3: Combining CHARINDEX with a Loop (Less Efficient, but Illustrative)

While generally not recommended for performance-critical scenarios due to iterative processing, it's possible to achieve this with a loop, especially if your list of characters is dynamic or very long. This method is more common in procedural code or when dealing with very specific, complex logic that might not fit neatly into a single PATINDEX pattern.

DECLARE @MyString VARCHAR(100) = 'This is a test string.';
DECLARE @SearchChars VARCHAR(10) = 'ist';
DECLARE @MinPos INT = 0;
DECLARE @CurrentChar CHAR(1);
DECLARE @i INT = 1;

WHILE @i <= LEN(@SearchChars)
BEGIN
    SET @CurrentChar = SUBSTRING(@SearchChars, @i, 1);
    DECLARE @CurrentPos INT = CHARINDEX(@CurrentChar, @MyString);

    IF @CurrentPos > 0
    BEGIN
        IF @MinPos = 0 OR @CurrentPos < @MinPos
        BEGIN
            SET @MinPos = @CurrentPos;
        END
    END
    SET @i = @i + 1;
END;

SELECT @MinPos AS FirstOccurrencePosition;

Using a WHILE loop to find the earliest position of any character

Practical Application: Cleaning Data

A common use case for finding one of several characters is data cleaning or parsing. For example, you might want to find the first special character in a string to truncate it, or locate the first delimiter from a set of possible delimiters.

DECLARE @ProductCode VARCHAR(50) = 'ABC-123/XYZ_456';

-- Find the first occurrence of '-', '/', or '_'
DECLARE @DelimiterPos INT = PATINDEX('%[-/_]%', @ProductCode);

SELECT
    CASE
        WHEN @DelimiterPos > 0
        THTHEN SUBSTRING(@ProductCode, 1, @DelimiterPos - 1)
        ELSE @ProductCode
    END AS CleanedProductCode;

Extracting a substring before the first delimiter