April 15, 2009 at 4:40 am
hi there,
I have the following script that creates two table that I am trying to join together on varchar columns using wildcards, but in SQL 2008 it is not returning any rows, but I think it should return two rows. Does anyone have an idea?
Cheers
Josh
Code;
Create table dbo.SourceData(
Id Int Identity (1,1),
Name nvarchar(50)
)
INSERT INTO dbo.SourceData (Name)
VALUES('Great Britain')
INSERT INTO dbo.SourceData (Name)
VALUES('Angola')
INSERT INTO dbo.SourceData (Name)
VALUES('United States of America')
Create table dbo.LookupData(
Id Int Identity (1,2),
Name nvarchar(50)
)
INSERT INTO dbo.LookupData (Name)
VALUES('Team Great Britain')
INSERT INTO dbo.LookupData (Name)
VALUES('USA All Stars')
INSERT INTO dbo.LookupData (Name)
VALUES('Angola Black Antelopes')
--Final select statement that returns no rows -
SELECT *
FROM SourceData s inner join LookupData l
ON s.Name LIKE '%' + l.Name + '%'
Mao Says RTFM
April 15, 2009 at 5:05 am
this works for me:
select
x.*,
y.*
from
(select
'Great Britain' as name
union
select
'angola'
union
select
'USA')x
inner join
(select
'team great britain' as name
union
select
'usa all stars'
union
select
'angola black antelopes'
)y
ON x.name like '%' + x.name + '%'
if i did:
ON x.name like '%' + y.name + '%'
then it brings nothing back
April 15, 2009 at 7:07 am
In your sample data you are trying to get 'Angola' to be like 'Angola Black Antelopes' which will not work as LIKE requires the Right Side to be a subset of the Left Side. If you change it to be 'Angola Black Antelopes' LIKE '%Angola%'. So do this:
Create table dbo.SourceData
(
Id Int Identity(1, 1),
Name nvarchar(50)
)
INSERT INTO
dbo.SourceData (Name)
VALUES
('Great Britain')
INSERT INTO
dbo.SourceData (Name)
VALUES
('Angola')
INSERT INTO
dbo.SourceData (Name)
VALUES
(
'United States of America'
)
Create table dbo.LookupData
(
Id Int Identity(1, 2),
Name nvarchar(50)
)
INSERT INTO
dbo.LookupData (Name)
VALUES
(
'Team Great Britain'
)
INSERT INTO
dbo.LookupData (Name)
VALUES
('USA All Stars')
INSERT INTO
dbo.LookupData (Name)
VALUES
(
'Angola Black Antelopes'
)
--Final select statement that returns no rows -
SELECT
*
FROM
SourceData s inner join
LookupData l
ON l.Name LIKE '%' + s.Name + '%'
DROP TABLE dbo.LookupData
DROP TABLE dbo.SourceData
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2009 at 7:22 am
Thanks Jack - do I feel dumb now, all I needed to do was switch around the direction of the like statement! Cheers.
Mao Says RTFM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply