February 14, 2006 at 1:47 pm
Ok I have a SP working however it is slow. I am looking for the best way to handle a situation.
Environment:
I have a table of data that needs to be matched with a record from another table. The match will result in an update to table 1 with the ID of the matched record in table 2.
Table 1 may have thousands of records that have yet to be processed. The field of concern here is a 'bigint'
Lets say that in table 1 we have the following record that require processing:
1216575854745
In table 2 I am looking for the maximum digits that match the left XX from table 1. Some values might be
1
121
1216
121657 *
1216574
12165747
The record with the Asterisk is the match in this case. The match is a result of the LEFT 6 digits matching. While the records above it match as well I am looking for the longest entry that matches.
Table # 2 has another column that indicates the length of the first value. In our match we have 6 digits so this length field would have a 6 in it.
I cycle through looking for the match by doing a LEFT(SourceValue, Length) = DestinationValue
I know that the maximum digits in length for the second table is say 12 so I start with LEFT(xxxx, 12) = xxxx if no match then length - 1, then try again at 11, and continue until a match is found, or I am at 0.
Any suggestions would be helpful.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 14, 2006 at 2:07 pm
Try this:
declare @Table1 Table(value varchar(14))
declare @Table2 Table(value varchar(12))
insert @Table1 Values('1216575854745')
insert @Table2 Values('1')
insert @Table2 Values('121')
insert @Table2 Values('1216')
insert @Table2 Values('121657')
insert @Table2 Values('1216574')
insert @Table2 Values('12165747')
select top 1 *
from @Table1 t1
inner join @Table2 t2
on t1.value like t2.Value + '%'
order by len(t2.value) desc
February 14, 2006 at 2:17 pm
Try this if table1 has multiple rows in it and you need the longest for each row:
declare @Table1 Table(value varchar(14))
declare @Table2 Table(value varchar(12))
insert @Table1 Values('1216574754745')
insert @Table1 Values('1216575854745')
insert @Table1 Values('1219905854745')
insert @Table1 Values('2219905854745')
insert @Table2 Values('1')
insert @Table2 Values('121')
insert @Table2 Values('1216')
insert @Table2 Values('121657')
insert @Table2 Values('1216574')
insert @Table2 Values('12165747')
select t1.Value, t2.value, mlen.maxlen
from @Table1 t1
inner join (select t1.Value, max(IsNull(len(t2.value), 0)) maxlen
from @Table1 t1
left outer join @Table2 t2
on t1.value like t2.Value + '%'
group by t1.Value
) mlen
on t1.Value = mlen.Value
left outer join @Table2 t2
on t1.value like t2.Value + '%' and len(t2.Value) = mlen.maxlen
February 15, 2006 at 11:41 am
February 16, 2006 at 3:19 am
Or this one, perhaps:
select t1.Value, max(cast(t2.Value as int))
from @Table1 t1
left join @Table2 t2
on t1.Value like t2.Value + '%'
group by t1.Value
I (and JeffB, I think) assume you want the first n digits to exactly match the value in table 2. In other words, 1216575854745 matches 121657 but not 1216575833, because it doesn't match the last two digits. Is this correct? Or is 1216575833 a better match than 121657 because the first 8 digits are the same?
February 16, 2006 at 8:25 am
Yes that is correct. I actually implemented a variation of this late yesterday and it is working very nicely. Thank you for your assistance. Now that I see it the path is obvious; just could not get my arms around that one.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply