Join where LIKE?

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))
donnatroniousAsked:
Who is Participating?
 
MikeTooleConnect With a Mentor Commented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
why the like word?

select a.*, b.*
      from dbo.la_missing_nate a
      join dbo.PipeFull b
      on b.test = a.acct
0
 
imitchieConnect With a Mentor Commented:
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 '\'

Open in new window

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.