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:
- 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.
- 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.)
- 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]
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.