[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Join where LIKE?

Posted on 2007-11-16
Medium Priority
165 Views
See tables below. The table PipeFull contains varchars up to 4,000 characters long that may or may not contain an acct number from table la_missing_nate.

The following SQL is running, but is this proper?

select a.*, b.*
from dbo.la_missing_nate a
join dbo.PipeFull b
on b.test like a.acct

CREATE TABLE [dbo].[PipeFull](
[test] [varchar](max))

CREATE TABLE [dbo].[la_missing_nate](
[acct] [nvarchar](50))
0
Question by:donnatronious
[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

LVL 55

Expert Comment

ID: 20302658
why the like word?

select a.*, b.*
from dbo.la_missing_nate a
join dbo.PipeFull b
on b.test = a.acct
0

LVL 27

Accepted Solution

MikeToole earned 1000 total points
ID: 20303771
For a LIKE comparison you need to specify wildcard characters. A % stands for any number of characters.

select a.*, b.*
from dbo.la_missing_nate a
join dbo.PipeFull b
on b.test like '%' + a.acct +'%'
0

LVL 25

Assisted Solution

imitchie earned 1000 total points
ID: 20303849
like Mike said, if you had 'Account X1234 has been archived' in PipeFull and 'X1234' in la_missing_nate
b.test like a.acct   means it has to math 'X1234' exactly, whereas
'%' + a.acct + '%' turns it into '%X1234%' which means

it can have any number of characters either side of a.acct
HOWEVER, there's a catch. if any of your account codes contain '%', your search expression becomes funny. The attached code far below shows you the what happens.  This code is more robust by escaping the %'s in the search string:

select a.*, b.*
from dbo.la_missing_nate a
join dbo.PipeFull b on b.test like '%' + replace(a.acct, '%', '\%') + '%' escape '\'
``````create table ab ( a varchar(max) )
insert into ab values ('test account X123 is closed')
insert into ab values ('test account X1 test 23 is closed')
create table ac ( a varchar(10) )
insert into ac values ('X123')
insert into ac values ('X1%23')

select * from ab join ac on ab.a like '%' + ac.a + '%' escape '\'
select * from ab join ac on ab.a like '%' + replace(ac.a, '%', '\%') + '%' escape '\'
``````
0

## Featured Post

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
###### Suggested Courses
Course of the Month14 days, 16 hours left to enroll