partial string comparison in SQL

Question

I’m not sure how to ask the question, how can I update the Model column in table 1 from table 2 new model. I have no idea if this can be done by SQL. If it can't be, I'll write some code that will loop thru the list.

The only common factor is the table2.model may exist somewhere within tabl1.description.

Table 1:

Description                         Model
------------------------------------------------
M114-1 vehicle description
vehicle  M114-2 description
track vehicle M116-3 description
troop M117 command vehicle

Table 2

Model               New Model
------------------------------
M114-1              M11412
M114-2              m11412
M114-3              m11412
M116-1              m11611

Possible duplicate of [SQL: Replace part of a column on multiple rows based on second table](http://stackoverflow.com/questions/7207727/sql-replace-part-of-a-column-on-multiple-rows-based-on-second-table) — Heinzi, May 11 '17 at 20:51

score 1 · Accepted Answer · answered May 11 '17 at 20:51

1

CHARINDEX can search sub-string like this.

Run as a select, validate, and the you can run the update!

SELECT *
-- UPDATE T1 SET MODEL=T2.[NEW MODEL]
FROM TABLE1 T1
JOIN TABLE2 T2 ON CHARINDEX(T2.MODEL,T1.DESCRIPTION)>0

answered May 11 '17 at 20:51

Dave C

7,272
1
19
30

score 0 · Answer 2 · answered May 12 '17 at 00:34

I would be inclined to use like for this purpose:

update t1
    set model = t2.newmodel
    from t1 join
         t2
         on t1.description like '%' + t2.model + '%';

To be a bit safer, you might want to insist that the model name in the description is surrounded by spaces (or at the beginning or end of the string):

update t1
    set model = t2.newmodel
    from t1 join
         t2
         on ' ' + t1.description + ' ' like '% ' + t2.model + ' %';

partial string comparison in SQL

2 Answers2