Put your symbols in one table, then do as follows@SpecialSymbols is your symbols table@TestData is your actual tableDECLARE @SpecialSymbols TABLE (Symbol CHAR(1) NOT NULL PRIMARY KEY)INSERT @SpecialSymbols VALUES ('#'), ('$'), ('['), (']'), ('%'), ('^'),( '*'),('('),('_'), ('"')DECLARE @TestData TABLE (StringToTest VARCHAR(100))INSERT @TestData VALUES ('ABS.DRG.drg.name[ABS.PAT.number%4]'), ('.PAT.number%4]'), ('test 1 [Using Square Brackets]'), ('[Test2@EmailAddress.com]'), ('No Special Symbols') SELECT StringToTest, MIN(Position) pos FROM @TestData OUTER APPLY ( SELECT CHARINDEX(Symbol, StringToTest) [Position], Symbol FROM @SpecialSymbols ) Symbols WHERE Position > 0 GROUP BY StringToTest-- This is for getting all special character's positionDECLARE @SpecialSymbols TABLE (Symbol CHAR(1) NOT NULL PRIMARY KEY)INSERT @SpecialSymbols VALUES ('#'), ('$'), ('['), (']'), ('%'), ('^'),( '*'),('('),('_'), ('"')DECLARE @TestData TABLE (StringToTest VARCHAR(100))INSERT @TestData VALUES ('ABS.DRG.drg.name[ABS.PAT.number%4]'), ('.PAT.number%4]'), ('test 1 [Using Square Brackets]'), ('[Test2@EmailAddress.com]'), ('No Special Symbols');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)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
--Chandu