I have two temp tables populated with Id's.

The #Master table is from one system while the #Extended table is from another system where people have added characters to the Id.

The issue I'm now having is to write a SELECT query to check to see if there is a match or if not to remove one character at a time until there is a match. As you can see below there will be instances where there won't be an exact match in that case I would just like to return one of the possible values (1-12L7QABC could return 1-12L7QO or 1-12L7QM).

CREATE TABLE #Master(Id nvarchar(15), ClientName nvarchar(35));
INSERT INTO #Master
VALUES('1-12L7QO', 'John Citizen'),
      ('1-12L7QM', 'Steve Smith'),
      ('1-10YL',   'Sarah Connor'),
      ('1-2CN9WN', 'Cathy Rodgers');

CREATE TABLE #Extended(ExtId varchar(15));
INSERT INTO #Extended
VALUES('1-12L7QO`'),
      ('1-12L7QABC'),
      ('1-10YL'),
      ('1-12L7QMTest');

The expected output is:

+---------------+---------------+-----------------------------------+
| Id            | BaseId        | Name                              |
+---------------+---------------+-----------------------------------+
| 1-12L7QO`     | 1-12L7QO      | John Citizen                      |
| 1-12L7QABC    | 1-12L7QO      | John Citizen                      |
| 1-10YL        | 1-10YL        | Sarah Connor                      |
| 1-12L7QMTest  | 1-12L7QM      | Steve Smith                       |
+---------------+---------------+-----------------------------------+

One method is to create all possible substrings and then do the match:

with m as (
      select m.id, m.ClientName
      from #Master m
      union all
      select left(m.id, len(m.Id) - 1), m.ClientName
      from m
      where m.Id <> ''
     )
select e.ExtId, m.Id, m.ClientName
from #Extended e outer apply
     (select top 1 m.*
      from m
      where e.ExtId like m.id + '%'
      order by len(m.id) desc
     ) m;

I don't want to argue that this is efficient, but it should do what you want. And, it's fine on a small data set.

more