Solved

SQL 2008 LIKE Clause

Posted on 2014-11-05
8
78 Views
Last Modified: 2015-03-04
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
Comment
Question by:thayduck
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 40424634
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
 

Author Comment

by:thayduck
ID: 40424731
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
 

Author Comment

by:thayduck
ID: 40424805
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 40424818
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
 

Author Comment

by:thayduck
ID: 40424835
I accepted your solution but it does not show up as accepted.
I did it twice.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40424930
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
 
LVL 39

Expert Comment

by:lcohan
ID: 40425117
That's fine - glad you got your work done.
0
 

Author Comment

by:thayduck
ID: 40425877
Thanks Scott, your code worked to.
Less code is always good.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question