• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

SQL Join with wild card

Hello experts,

I'm having a bit of trouble with a SQL query.  I'm using MS SQL 2005 Enterprise.  I'm attempting to return records from two tables in cases where values in table1.field1=values in table2.field1+a wildcard.  For example, if table1.field1 contains record 5478, I'd like to return values in every case where records in table2.field1 contain that value, say records 5478-01, 5478-04, 5478-06, etc.  I have experimented with different kinds of joins and placements of wildcards, but, so far, I've only been able to get returns where identical values are present in both tables.  For example:

select gl20000.orctrnum, rm30201.apfrdcnm from gl20000
join
rm30201
on gl20000.orctrnum+'%' like '%'+rm30201.apfrdcnm+'%'
where actindx='6'

only returns records where identical records exist in both tables.  Moving/removing wildcards has no effect, which leads me to believe I'm missing something really obvious.  Using, say, left join just gives me all records in table1 with nulls where non-identical values exist in table two.  There are tons of records that satisfy these conditions, so...

Okay, any help you guys can provide would be awesome.  Thanks!
0
bradlee27514
Asked:
bradlee27514
  • 7
  • 6
  • 5
  • +1
1 Solution
 
ransommuleCommented:
Try something like...

SELECT gl20000.orctrnum, rm30201.apfrdcnm
FROM gl20000 , rm30201
WHERE  gl20000.orctrnum LIKE '%'+ rm30201.apfrdcnm + '%'
AND actindx='6'
0
 
bradlee27514Author Commented:
No dice.  Giving same results.
0
 
exodusterCommented:
So try thisone:
select gl20000.orctrnum, rm30201.apfrdcnm from gl20000
join
rm30201
on CHARINDEX(gl20000.orctrnum, rm30201.apfrdcnm) > 0
where actindx='6'
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ransommuleCommented:
What about this ?

SELECT gl20000.orctrnum, rm30201.apfrdcnm
FROM gl20000 , rm30201
WHERE  gl20000.orctrnum LIKE rm30201.apfrdcnm + '%'
0
 
bradlee27514Author Commented:
Still no dice.
0
 
lwadwellCommented:
In your SQL ... which is the 'table2' from your description out of gl20000 and rm30201?  
What happens if you reverse the columns, e.g.

  select gl20000.orctrnum, rm30201.apfrdcnm
  from gl20000
  join rm30201 on  rm30201.apfrdcnm LIKE '%'+gl20000.orctrnum+'%'
  where actindx='6'

Just to be double sure ... both columns are char/varchar?
0
 
ransommuleCommented:
select SUBSTRING( Test.ValueA,0,LEN(Test2.Nombre) + 1)  
from Test, Test2
where Test2.Nombre like SUBSTRING( Test.ValueA,0,LEN(Test2.Nombre) + 1)  + '%'
0
 
exodusterCommented:
If columns are type of VARCHAR, my answer is working. I checked it. If not, just cast it. Like that CAST(nameOfColumn AS VARCHAR(MAX)).
0
 
bradlee27514Author Commented:
table1=gl20000, table 2-rm30201
If gl20000 contains record 005702, we want returns for any instance in which a record in the correct field of table two contains that value, such as 005702-01, 005702-06, etc.

select * from gl20000 where actindx='6' and orctrnum like '%005702' returns one record

select * from rm30201 where apfrdcnm like '%005702%' returns 25 records

Both of these are examples of what we're looking for.  Need for the query to search for this kind of thing automatically and join the results.
As for char/varchar... pardon my lack of expertise, but I'm not sure about this one.  It seems like they should be the same, as both fields populate with the same kinds of data, from the same source.  Not sure though.  How can I check this?
0
 
ransommuleCommented:
This MUST work...

SELECT gl20000.orctrnum, rm30201.apfrdcnm
FROM gl20000 , rm30201
WHERE  rm30201.apfrdcnm LIKE gl20000.orctrnum  + '%'
0
 
lwadwellCommented:
What does
      select * from gl20000 where actindx='6' and orctrnum = '005702'
return?
I asked because you used
    like '%005702'
and if there are leading characters on the value ... even blanks, it could affect the join.

The char/varchar is part of the table definition ... the datatype of the column.  There are various methods depending on the tool you are using, one way is the following command.
    exec sp_columns gl20000
0
 
exodusterCommented:
You can just do that:

select gl20000.orctrnum, rm30201.apfrdcnm from gl20000
join
rm30201
on CHARINDEX(CAST(gl20000.orctrnum AS VARCHAR(MAX)), CAST(rm30201.apfrdcnm AS VARCHAR(MAX))) > 0
where actindx='6'

To check type of column gl20000.orctrnum (in database where are those tables):
SELECT t.name
FROM sys.columns c JOIN
      sys.types t
            ON c.object_id = OBJECT_ID('dbo.gl20000') AND c.system_type_id = t.system_type_id
WHERE c.name = 'orctrnum'

if table gl20000 is in dbo schema.
0
 
bradlee27514Author Commented:
exo:

column type of both = char.  Used your last suggestion, it gave records where fields contained identical data.
'pymnt000000005207' is an example of the full orctrnum I'm looking for, so

select * from gl20000 where actindx='6' and orctrnum = 'pymnt000000005207' returns the single example record I'm looking for from gl20000.

select * from rm30201 where apfrdcnm like 'pymnt000000005207%' returns the 25 records I'm looking for from rm30201.
0
 
lwadwellCommented:
try

select *
from gl20000
join rm30201 on  RTRIM(rm30201.apfrdcnm) LIKE '%'+gl20000.orctrnum+'%'
where actindx='6'
0
 
bradlee27514Author Commented:
lwadwell,

this query produced zero results.
0
 
lwadwellCommented:
Oh (or D'Oh!) ... I probably should RTRIM() both values, i.e.
   RTRIM(rm30201.apfrdcnm) LIKE '%'+RTRIM(gl20000.orctrnum)+'%'
0
 
exodusterCommented:
Sry for my eanglish.
I dont get the problem.
What is the result with my suggestion? To many records? Or only one row with identical data?
If you dont want row with identical data, just add AND CAST(gl20000.orctrnum AS VARCHAR(MAX)) <> CAST(rm30201.apfrdcnm AS VARCHAR(MAX)) at ON part of JOIN
0
 
bradlee27514Author Commented:
Ding, ding, ding!  We have a winner!  Now... if you can explain to me WHY it worked, and why the others didn't, I'd really appreciate it...  I know what RTRIM does, but it's not clear to me why it worked here.
Thanks a bunch!
0
 
bradlee27514Author Commented:
Great work!
0
 
exodusterCommented:
buuu, what did i wrong? :) this query is working at my example. And with RTRIM doesn't.
Try to check collation of those columns.
0
 
lwadwellCommented:
Because your columns where CHAR ... they are fixed width unlike varchar.  So you columns have trailing spaces ... which meant that
   'pymnt000000005207   '+'%' is not like 'pymnt000000005207-01  '
because of the spaces.

Thr rtrim() removed the spaces so you get
   'pymnt000000005207'+'%' is like 'pymnt000000005207-01'
0
 
exodusterCommented:
ok :) now it's  clear to me too. I'm just not used to working with CHAR type of columns :)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 7
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now