?
Solved

Join where LIKE?

Posted on 2007-11-16
3
Medium Priority
?
168 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

839 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