How can I find the locations of all special symbols in a varchar?

Question

I've tried using patindex and charindex, but it seems that neither of them will easily do what I want. Charindex only wants to search for one symbol at a time, and patindex doesn't allow a "search from" index, not allowing me to cycle through, finding all indexes of symbols. What are my options?

Answer 1

With no specific details about what your "Special Symbols" are, how the are stored and what output you are expecting there is a bit of guess work involved, but I think the basic principals of my answer can be applied regardless. The key to getting all occurances is using a recursive CTE along with OUTER APPLY. Each time the CTE loops it replaces a special character with a space until no special characters remain, storing the location of character as it goes along.

Sample Data:

DECLARE @SpecialSymbols TABLE (Symbol CHAR(1) NOT NULL PRIMARY KEY)
INSERT @SpecialSymbols VALUES ('@'), ('.'), ('['), (']')

DECLARE @TestData TABLE (StringToTest VARCHAR(100))
INSERT @TestData VALUES 
    ('test 1 [Using Square Brackets]'), 
    ('[Test2@EmailAddress.com]'), 
    ('No Special Symbols')

Actual Query

;WITH CTE AS
(   SELECT  *, STUFF(StringToTest, Position, 1, ' ') [ReworkedString]
    FROM    @TestData
            OUTER APPLY
            (   SELECT  CHARINDEX(Symbol, StringToTest) [Position], Symbol
                FROM    @SpecialSymbols
            ) Symbols
    WHERE   Position > 0
    UNION ALL
    SELECT  StringToTest, Symbols.Position, Symbols.Symbol, STUFF(ReworkedString, Symbols.Position, 1, ' ') [ReworkedString]
    FROM    CTE
            OUTER APPLY
            (   SELECT  CHARINDEX(Symbol, ReworkedString) [Position], Symbol
                FROM    @SpecialSymbols
                WHERE   Symbol = CTE.Symbol
            ) Symbols
    WHERE   Symbols.Position > 0
)

-- CTE NOW LOOKS LIKE:
--  | test 1 [[Using Square Brackets]   |   8   |   [   | test 1  [Using Square Brackets]
--  | test 1 [[Using Square Brackets]   |   30  |   ]   | test 1 [[Using Square Brackets 
--  | [Test2@EmailAddress.com]          |   20  |   .   | [Test2@EmailAddress com]
--  | [Test2@EmailAddress.com]          |   7   |   @   | [Test2 EmailAddress.com]
--  | [Test2@EmailAddress.com]          |   1   |   [   |  Test2@EmailAddress.com]
--  | [Test2@EmailAddress.com]          |   24  |   ]   | [Test2@EmailAddress.com 
--  | test 1 [[Using Square Brackets]   |   9   |   [   | test 1   Using Square Brackets]

SELECT  a.StringToTest, COALESCE(Location, '') [SpecialSymbolLocations]
FROM    @TestData a
        LEFT JOIN
        (   SELECT  DISTINCT
                    StringToTest,
                    -- THIS MERELY CONCATENATES ROWS INTO COLUMNS TO GET COMMA SEPARATED LIST
                    STUFF(( SELECT  ', ' + CONVERT(VARCHAR, Position)
                            FROM    CTE b
                            WHERE   a.StringToTest = b.StringToTest
                            ORDER BY Position
                            FOR XML PATH('')
                    ), 1, 2, '') [Location] 
            FROM    CTE a
        ) b
            ON a.StringToTest = b.StringToTest

The CTE can be manipulated as you please really, however for the sake of completeness I have added a final query using the SQL server XML Extension to concatenate the locations of the special symbols into a comma separated list and put these next to each of the original strings. So the final output is as follows:

| StringToTest                      | SpecialSymbolLocations    |
|-----------------------------------|---------------------------|
| test 1 [[Using Square Brackets]   | 8, 9, 31                  |
| [Test2@EmailAddress.com]          | 1, 7, 20, 24              |
| No Special Symbols                |                           |