Is there a way to get the Nth index of a specified char in T-SQL? (Without Substring!)

Question

Hello, I am trying to get the index of the Nth character inside a varchar(MAX). Right now I am cutting off the front end of the varchar so I only have to check for the first but With extremely large varchars the process is getting way too slow. I would like to parse through the varchar(max) and just keep track of what index I am at and get the next character I want AFTER that index. If I could do this without having to constantly cut off the front of a large varchar constantly I think it would increase performance a good amount. Thanks.

EDIT: Right now to parse a large amount of text I use CHARINDEX to get the index of the char, THEN I SUBSTRING the text to remove up to that first index. Now I call CHARINDEX again (which effectively retrieves the 2nd index of that character inside the text). However, this SUBSTRINGING is very taxing on the system and I want to avoid it.

EDIT: Ah, sorry my title was very misleading, it should be more straight forward now.

Answer 1

I think the CHARINDEX or SUBSTRING functions will solve your problem.

Answer 2

The built-in string functions are rather limited in T-SQL. You need to combine SUBSTRING(), CHARINDEX(), STRPOS() to do this, but it not fast operations.

Also you can use a tally table.

But i think the best way is to use CLR procedure for task like your. It will be more efficient and fast.

Answer 3

One way to increase performance when doing procedural work (as opposed to set-based work) is to use CLR Integration with SQL Server. I got this tip from Chris Lively in this answer when asking a question about performance. Although I haven't tested it myself, the documentation around CLR integration with SQL server suggests that the CLR is much more performant than T-SQL for procedural operations. For set-based operations, though, use T-SQL.