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]