At finding a substring, find the end position as well

Question

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),

• hä occurs in 'Häger' at position 1 and ends at position 2 and
• hä 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?

Answer 1

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.

Answer 2

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]