0

I want To get value of column when it exist in content value of column by sql server 2016

For Example:

Table1:

Title1 
----
Jack
Benz
Volvoo

Table2:

    Description              Names
    ----                    ---------
  The Jack isnt A122           NULL
  Some  B1234 Benz             NULL
  This Volvoo Are C            NULL
  This Maria Are D             NULL
  Michael Jackson              NULL

example:

Search Row By Row From table 1 in the row by row column Description in Table 2 and get value and insert to cloumn Names

IF Jack or Benz Or Volvoo exist in The Jack isnt A122 get Jack and Update column Names in Table 2

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack

For 'Jack owns a Benz' that matches 2 titles (Jack and Benz), update with either one of them.

For Other Rows in Table 2 same:

    Description              Names
    ----                    ---------
  The Jack isnt A122           Jack
  Some  B1234 Benz             Benz
  This Volvoo Are C            Volvoo
  This Maria Are D             Others
  Michael Jackson              Others
  • The tables do not have primary keys.
  • Table1 (Title1) is UNIQUE.
2

Under some simplistic / naive assumptions (that match the given data):

  • The descriptions have only spaces and no other whitespace or connecting characters (dots, comma, question marks, dashes, etc.)
  • Titles are single words (no spaces, no extra characters either).

We can use LIKE to check whether a description contains a title:

concat(' ', description, ' ') LIKE concat('% ', title, ' %')

to first check that the matches are according to requirements:

select s.description, coalesce(t.title, 'Others') as title
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

and then do the update:

update s 
set s.names = coalesce(t.title, 'Others') 
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

Test in dbfiddle.uk

  • Thanks for solution my friend. why double data for one value ?one null other correct value. – cyrus2500 Feb 17 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.