[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
3
Medium Priority
?
165 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
[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
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 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

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

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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

649 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