SQL 2008 LIKE Clause

Using a Like Clause, I want to read table A and if I find a match from Table B somewhere in Fld1 of Table A, I want to update Fld1 with the match from Table B.

Table A

Fld1

aaahopaaa
bbbfrogzzz
doghhhhhh
xxbirdxxxz

Table B

Fld2

hop
dog
frog
cat

So when all done, Table A should look like below:

Fld1

hop
frog
dog
xxbirdxxxz
thayduckProgrammer AnalystAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
Yeah sorry...I had too many quotes as I ripped it out of a dynamic string.

create table table_A (Fld1 sysname);
create table table_B (Fld2 sysname);

insert into table_A select 'aaahopaaa';
insert into table_A select 'bbbfrogzzz';
insert into table_A select 'doghhhhhh';
insert into table_A select 'xxbirdxxxz';

insert into table_B select 'hop';
insert into table_B select 'dog';
insert into table_B select 'frog';
insert into table_B select 'cat';

select * from table_A
select * from table_B

update table_A set Fld1 = b.Fld2
from table_B b
where exists(select * from table_A where Fld1 like '%'+b.Fld2+'%')
            and table_A.Fld1 like '%'+b.Fld2+'%'

select * from table_A
select * from table_B
0
 
lcohanDatabase AnalystCommented:
I guess it would be something like the code below but PLEASE run it in a test environment first:

update table_A set Fld1 = b.Fld2
from table_B b
where exists(select * from table_A where Fld1 like '''%'+b.Fld2+'%''')
      and table_A.Fld1 like '''%'+b.Fld2+'%'''
0
 
thayduckProgrammer AnalystAuthor Commented:
No match.

update WKLDStaging.RadioWorkRTI set orgloc = g.DeviceName
from WKLDStaging.RadioGeography g
where exists(select * from WKLDStaging.RadioWorkRTI where orgloc like '''%'+g.DeviceName+'%''')
      and WKLDStaging.RadioWorkRTI.orgloc like '''%'+g.DeviceName+'%'''


orgloc actually =  (_o__ RTI 4149(Amos RTI))HDX-2 Telco
There is a record with a device name =  Amos RTI
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
thayduckProgrammer AnalystAuthor Commented:
This worked:

update WKLDStaging.RadioWorkRTI set orgloc = g.DeviceName
from WKLDStaging.RadioGeography g
where exists(select * from WKLDStaging.RadioWorkRTI where orgloc like '%' + g.DeviceName + '%')
      and WKLDStaging.RadioWorkRTI.orgloc like '%' + g.DeviceName + '%'
0
 
thayduckProgrammer AnalystAuthor Commented:
I accepted your solution but it does not show up as accepted.
I did it twice.
0
 
Scott PletcherSenior DBACommented:
You don't need two separate comparisons [an exists with a like and an expression with a like], a single join will do it:


update w
set orgloc = g.DeviceName
from WKLDStaging.RadioWorkRTI w
inner join WKLDStaging.RadioGeography g on
    w.orgloc like '%' + g.DeviceName + '%'
0
 
lcohanDatabase AnalystCommented:
That's fine - glad you got your work done.
0
 
thayduckProgrammer AnalystAuthor Commented:
Thanks Scott, your code worked to.
Less code is always good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.