Self-join with LIKE and wildcards

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Self-join with LIKE and wildcards

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2012-09-17 : 08:54:45

Hi,

I've got a table that I want to search, looking for rows where one field contains a value of another field. So, for example:


RowId  SourceId    ResultIds
============================
1      123         456,789
2      456         123,456
3      789         123,456
4      010         456,010

Would return rows 2 and 4, because the ResultId contains the SourceId.

Query would look something like this:


SELECT TOP 100 * MyTable r1
inner join myTable r2
on r1.RowId = r2.RowId
where r1.ResultIds like '% r2.SourceId %'

But obviously you can't use the self-joined column dynamically in that manner.

Guess I could do it with dynamic SQL but I'd rather not. Is there a way?

Cheers,
Matt

singularity
Posting Yak Master

153 Posts

Posted - 2012-09-17 : 09:04:24

[code]
SELECT TOP 100 *
from MyTable r1
inner join myTable r2
on r1.RowId = r2.RowId
where r1.ResultIds like '%' + r2.SourceId + '%'
[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-17 : 09:06:33

No need to self join

DECLARE @Table Table (rowId int,sourceid char(3),resultsid varchar(20))
INSERT INTO @Table
VALUES
(1,'123','456,789'),
(2,'456','123,456'),
(3,'789','123,456'),
(4,'010','456,010')

select t1.*
from @Table t1
where t1.resultsid like '%'+t1.sourceid+'%'

Jim

Everyday I learn something that somebody else already knew

mattt
Posting Yak Master

194 Posts

Posted - 2012-09-17 : 09:07:18

D'oh! I really should have tried that, just assumed it wouldn't work!

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources