June 4, 2009 at 11:48 am
I have to write a query which i need to join 2 columsn with diff format of numbers.....I need to select the rows which they have same 9 digits they can be in any order or format.
I have 9 digit number.I need to compare that in any format
For Example : i have 123456789 number in one column A in TAB A and i have 234567819 in column B TAB B when i join these two tables on this Column A and Coumn B i need to select this ............
Thanks
Ragh
June 4, 2009 at 12:26 pm
First, take the data modeler out back and beat him with a rubber hose. We're dealing with relational data here, right? That is what a RDBMS is used for. How on earth can you assume that columns with those 2 values can be equal or related? Can you expand on the business reasons for what you are trying to do?
June 4, 2009 at 12:35 pm
June 4, 2009 at 12:55 pm
Here's what I came up with:
create table #T (
ID int identity primary key,
Col char(9));
insert into #T (Col)
select '123456789' union all
select '987654321' union all
select '111222333';
insert into #T (Col)
select '111112222' union all
select '121212121';
insert into #T (Col)
select '111113333';
;with
Parsed as
(select ID, substring(Col, number, 1) as Sub
from #T
inner join dbo.Numbers
on Number between 1 and 9),
SubElements as
(select ID, count(distinct Sub) as DistElements
from Parsed
group by ID),
Subs as
(select ID, Sub, count(*) as Qty
from Parsed
group by ID, Sub),
Matches as
(select S1.ID as ID1, S2.ID as ID2, count(*) Joins
from Subs S1
inner join Subs S2
on S1.ID < S2.ID
and S1.Sub = S2.Sub
and S1.Qty = S2.Qty
group by S1.ID, S2.ID)
select ID1, ID2
from Matches
inner join SubElements SE1
on Matches.ID1 = SE1.ID
inner join SubElements SE2
on Matches.ID1 = SE2.ID
and Joins = SE1.DistElements
and Joins = SE2.DistElements;
You'll almost certainly be better off replacing the CTEs with temp tables for this one, but that's easy enough to do.
Does that do what you need? Am I reading your situation correctly?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2009 at 1:48 pm
Where this table came from dbo.Numbers
June 5, 2009 at 7:26 am
Sorry. Forgot to include the script for that.
-- Creates a Numbers table for various uses. This script is SQL 2005 or later only
create table dbo.Numbers (
Number int primary key);
go
insert into dbo.Numbers (Number)
select top 10000 row_number() over (order by t1.object_id)
from sys.all_objects t1
cross join sys.all_objects;
go
create synonym dbo.Tally for dbo.Numbers;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply