SQL - Find the longest common sub-string match

Use charindex()

Also, when there are different join cases, can use case in the function, like right(string, case when...)

e.g.