I was tearing my hair out recently (I didn't have much hair left in the first place, which makes it all the worse!) when the CHARINDEX function was returning 0 even though the string I was looking for definitely existed in the input string!

The database column being searched was of type nText and it turns out that the function concatenates the column value (at what length, I don't know), unless you CAST it to a more usable data type.

So, I added a CAST([ColumnName] AS nVarChar(max)) to the script and hey presto it starts working!!

I would be very appreciative of a message or something to let me know that the input string was concatenated.

Hope this saves someone else from tearing some of their precious hair out!

Nat