CODEWITHSUNDEEP
sqlsql-server
Draco
Draco

Reputation: 71

partial string comparison in SQL

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

Upvotes: 0

Views: 2613

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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 + ' %';

Upvotes: 0

Dave C
Dave C

Reputation: 7392

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

Upvotes: 1

Related Questions