Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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
0
thayduck
Asked:
thayduck
  • 4
  • 3
2 Solutions
 
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
 
thayduckAuthor 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
 
thayduckAuthor 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
thayduckAuthor 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
 
thayduckAuthor Commented:
Thanks Scott, your code worked to.
Less code is always good.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now