longest matching number - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

longest matching number

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-22 : 11:44:41

hi sqlteam,
I have a table with numbers that I want to match to another table with numbers.

eg.

table 1 contains

number
11122
13234
13266

table 2 contains
111
1323
132
1322
I want to match :

11122 with 111
13234 with 1323
13266 matches with 132

do you know how to do this ?

thank you for any help.
jamie

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-22 : 11:50:08

Hi,

What is your expected output ??

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-22 : 11:51:20

something like :

number match
11122 111
13234 1324
13266 132

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-22 : 12:00:23

select * from (select
t1.number as number1, t2.number as number2, rank() over (partition by t1.number order by len(t2.number) desc) as rnk
from @t1 t1 cross join @t2 t2
where patindex(t2.number+'%',t1.number)>0
)t where rnk = 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-22 : 12:14:46

thank you, strangly I get this error :
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the patindex operation.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-22 : 12:15:53

Hi, try this

drop table #temporary
declare @temp table (number varchar(1024))

insert into @temp
select '11122' union all
select '13234' union all
select '13266'

declare @temp1 table (number1 varchar(1024))

insert into @temp1
select '111' union all
select '1323' union all
select '132' union all
select '1322'

select t.number,number1 as match into #temporary
from @temp t
inner join @temp1 t1 on t.number like '%'+t1.number1+'%'

select number,match from ( select number ,match, row_number() over ( partition by number order by len(match) desc) as sno from #temporary ) t
where t.sno = 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-22 : 12:26:07

hi, I fixed this error but recreating my table, but I get no values ?!

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-22 : 12:33:03

trying raky's version.

works for some numbers but not all.
eg, I have 1744631131 = 113 ?! surely that would be 1744

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-22 : 13:08:01

Cross out the first wildcard:

quote:
Originally posted by raky

Hi, try this

drop table #temporary
declare @temp table (number varchar(1024))

insert into @temp
select '11122' union all
select '13234' union all
select '13266'

declare @temp1 table (number1 varchar(1024))

insert into @temp1
select '111' union all
select '1323' union all
select '132' union all
select '1322'

select t.number,number1 as match into #temporary
from @temp t
inner join @temp1 t1 on t.number like ~~'%'+~~t1.number1+'%'

select number,match from ( select number ,match, row_number() over ( partition by number order by len(match) desc) as sno from #temporary ) t
where t.sno = 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-23 : 05:35:51

perfect.
thank you all.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-23 : 10:36:09

also there is no need for the temp table:


select number,match from ( select t.number,number1 as match, row_number() over ( partition by number order by len(match) desc) as sno from @temp t inner join @temp1 t1 on t.number like t1.number1+'%') t
where t.sno = 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-12-23 : 11:01:52

I've inserted the data into another table just to speed things up.
but thank you for the advice and help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources