Solved

SQL 2008 LIKE Clause

Posted on 2014-11-05
8
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 40

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

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 40

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 44
SQL Sum of items in two tables not equal. 5 62
SQL server client app 3 73
store vs query adhoc - no show rows 4 37
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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