SQL: Can I use CHARINDEX to return the best match not just the first match?

Question

http://sqlfiddle.com/#!6/5ac78/1

Not sure if that fiddle will work. I want to return code 2 from the join on CHARINDEX.

As another example, I have a Description table (dt) that looks like this:

ID      Description                         Code
158     INTEREST                            199
159     INTEREST PAID                       383
160     INTEREST PAYABLE ON     ACCOUNT     384

And a master table (mt) with entries like this:

ID      Narrative                       Code
1       INTEREST PAID                   NULL

I need to set the Code on the master table to 383. When I do an INSERT based on a JOIN using CHARINDEX(dt.Description, mt.Description) > 0, it sets the mt.Code to 199 every time.

How I can update the master table to pull the Code from Description table with the best match, not just the first matching instance?

Thanks!

Answer 1

You could just use a simple JOIN to find a match with a LEFT JOIN to eliminate all but the longest match;

UPDATE t1 
SET t1.codeA = t2_1.codeB
FROM table1 t1
JOIN table2 t2_1      
  ON CHARINDEX(t2_1.colB, t1.colA) > 0
LEFT JOIN table2 t2_2 
  ON CHARINDEX(t2_2.colB, t1.colA) > 0
 AND t2_1.codeB <> t2_2.codeB 
 AND LEN(t2_2.colB) > LEN(t2_1.colB)
WHERE t2_2.colB IS NULL;

An SQLfiddle to test with.

Note that it's (probably) not possible to make a CHARINDEX query like this one (or your original query) use indexes, so the query may be very slow for large amounts of data.

Also, always test first before running SQL updates from random people on the Internet on your production data :)