Solved

Join where LIKE?

Posted on 2007-11-16
3
138 Views
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))
0
Comment
Question by:donnatronious
3 Comments
 
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
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 250 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

by:imitchie
imitchie earned 250 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

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now