Join where LIKE?

Posted on 2007-11-16
Medium Priority
Last Modified: 2010-03-19
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))
Question by:donnatronious
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20302658
why the like word?

select a.*, b.*
      from dbo.la_missing_nate a
      join dbo.PipeFull b
      on b.test = a.acct
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 +'%'
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 '\'

Open in new window


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

588 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