A LIKE clause can test if a string occurs in another and the CHARINDEX function can give the start position of the first match.

In my case, I'm interested in the end position though, which is, due to the intricacies of collations, not derivable from the start location. For example, in a German collation (German_PhoneBook_100_CI_AS_SC_UTF8),

  • occurs in 'Häger' at position 1 and ends at position 2 and
  • occurs in 'Haeger' at position 1 and ends at position 3.

The problem this is for is to mark the matching part of a search result text for the users benefit.

I've been thinking about reversing the strings, but then I still can get only the first match with CHARINDEX and in that reversed case I'd need the last.

Any ideas anyone?

  • I don't believe there's any fool-proof way to get this via pure T-SQL. You might get close by taking a substring, starting at the the first character, of a length up to 2x the length of the what you are looking for, and doing a CHARINDEX after reversing both. This would get many cases, but wouldn't always get cases of multiple combining characters, nor would it prevent falsely finding repeated patterns. Perhaps additional checks could be done to prevent false positives, but this is kinda messy (though quite interesting) no matter what. I'm trying something and will let you know. Sep 8, 2022 at 16:24

2 Answers 2


CHARINDEX will give the dart of a match. Use STUFF to insert @sep. This will break the match. Step forward one place at a time until the match is restored. This will give the end of the source.

haeger   match at position 1   
h|aeger  no match    
ha|eger  no match    
hae|ger  match    

So the match stretches from position 1 to 3 inclusive.

If there are multiple matches in the original string this will show after the first time @sep is stuffed. In this case the original can be truncated from there and searched in that way.

Sorry, I can't type this up cleanly at the moment. Maybe tomorrow. Hopefully it's useful none the less.

  • This requires a loop though and I already found a way that doesn't need looping.
    – John
    Sep 10, 2022 at 1:04

Following is a new attempt which I believe works but is much more hacky than the first that was posted in this answer.

Building on the idea to use replace but having to deal with the fact that replace replaces all and not just the first occurrence, I now replace the matches with something containing an identifiable separator that I can find with charindex to separate the rest. I can then remove the rest and look at the length of the remainder.

However, let's make the following assumptions to make our life a bit easier, it gets convoluted enough even with these restrictions in place:

  1. The search string is assumed to be at the beginning to the to-be-searched source. This is the case I actually need for my problem, but a more general solution likely exists too.
  2. The separation character is not in the source. In my own case, I can choose an exotic character and live with this feature not working for that one rare string where it actually does occur. (I check first of course.)
  3. In order not having to specify the collation all over the place in the query, I assume the query to run in a database with collation German_PhoneBook_100_CI_AS_SC_UTF8 - make sure you do the same or add the collation specifiers when running this.

First, here's a programmatic version:

declare @sep char(1) = '|'
declare @source varchar(60) = 'haegerhae'
declare @tofind varchar(60) = 'hä'

declare @helper varchar(61) = concat(@tofind, @sep)

declare @temp varchar(60) = replace(@source, @tofind, @helper)
declare @l int = charindex(@sep, @temp, 1)

select @temp temp, left(@source, @l) [match];

The remainder shows as hae, which also tells us the end position by its length.

Here's the inlined expression:

select left(@source, charindex(@sep, replace(@source, @tofind, concat(@tofind, @sep)), 1)) [match]
  • Clever. However, does not work with repeated patterns. Try 'hello haegerhaeger' and 'hello haegerhaegerhaeger' as test patterns. Sep 8, 2022 at 16:37
  • @SolomonRutzky Dammit.
    – John
    Sep 8, 2022 at 16:38
  • Also, the value returned is the length, not the end position (unless the match starts at character 1 of the string). Just FYI. Also, even when not accounting for expansions (such as you are initially looking for), the same behavior occurs in simple duplicates such as: 'hello mississippi' for @source, and 'iss' for @tofind. Sep 8, 2022 at 16:43
  • 1
    This new approach definitely has merit, but I don't think it's there yet. Try the following as the test value for @source: 'ha haehägerhä'. Sep 8, 2022 at 21:23
  • 1
    I just tested the new approach and it looks good, but might have some extraneous code at the end. I don't think you need the whole "unwanted" part. You already have the position of the first separation character, so just substring using that rather than replacing a large chunk of stuff with an empty string (which is far less efficient). I got the same result (using the CTE) by 1) removing the "unwanted" CTE, and 2) replacing the final SELECT with: select LEFT(@source, v) from l. Sep 28, 2022 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.