Digit by digit subtraction
-
Hi All,
I am a software programmer from India.
I have 2 strings lets say str1 = '3456' str2 = '3754'
What i need is the digit by digit subtraction of the above strings. For ex.
abs(3-3)+abs(4-7)+abs(5-5)+abs(6-4) = 5
In real time the string's length is 36. Hence in order to get the above result...we have to substring taking 1 value at a time
and do the subtraction. So we have to run the loop 36 times. We have to do this for 40 lacs records. The system's performance
goes for a toss. Can u please suggest me a more effiecient way of doing this...?
Looking forward to an early reply.
Thanks & Regards
Rajesh
-
I don't know how to do it more efficient - ie setbased rather than looping. I suppose you'd still have to loop through each char (digit) in the string and do the maths as long as you iterate.
You could stick the loop inside a function, though. I'd guess that it will take some time anyway to through all your records regardless..
create function dbo.absSubtract ( @str1 varchar(36), @str2 varchar(36) )
returns int
as
begin
declare @i int, @j-2 int, @k int, @abs int
select @i = 1, @abs = 0
while @i <= len(@str1)
begin
set @j-2 = cast(substring(@str1, @i, 1) as int)
set @k = cast(substring(@str2, @i, 1) as int)
set @abs = @abs + abs(@j - @k)
set @i = @i + 1
end
return(@abs)
end
go
select dbo.absSubtract('3456', '3754')
go
-- from a table
create table #x ( str1 varchar(36) not null, str2 varchar(36) not null )
go
insert #x select '3456', '3754'
go
select dbo.absSubtract(str1, str2)
from #x
go
/Kenneth
-
Thanx Kenneth...
Acually there are approximately 4 million records ..therefore one has to run the loop 36 * 4 million times....so its taking a lot of time...
Nevertheless thanks for ur reply....
Rgds,
Rajesh
-
I assume that it's a one-time operation? If you gotta do it, you gotta do it. One way or the other.
/Kenneth
-
I think I have a faster (set-based) solution (the creation of the Numbers table is stolen from one of Remi's posts):
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
create table datatable(t1 varchar(36), t2 varchar(36))
go
insert into datatable select '3456', '3754'
union all select '2345', '6789'
select d.t1, d.t2, sum(abs(cast(substring(d.t1, PkNumber, 1) as int) - cast(substring(d.t2, PkNumber, 1) as int)))
from datatable d cross join Numbers N where N.PkNumber<=len(d.t1) and N.PkNumber<=len(d.t2)
group by d.t1, d.t2
drop table datatable
go
-
Hi Jesper,
Thanx a lot !!!!!
Iam getting the right results...i will just chek on 4 million records and see the timings....
Thanx again,
Rajesh
-
You might wanna do that in batch... unless you have LOADS of ram and a quiet morning on the server. This kind of operation takes a lot of ram/processor power.
-
You are welcome - please post the result of your test...
Also note that if your numbers always have the same length - 36 - then you may replace
N.PkNumber<=len(d.t1) and N.PkNumber<=len(d.t2)
by the following, which should be faster:
N.PkNumber<=36
-
Which will be faster actually...
Have you tested that EACH and EVERY single row as 36 numbers in each column. If not I'd add another check to make sure both columns are the same length.
-
No both the strings are of 36 each.....
-
No both the strings are of 36 each.....
-
Jesper,
Its amazing....... it took me 40 secs to process 1 million records.
Thanx a ton !!!!
Rajesh
-
Yup, that's the power of the numbers table
. -
I didn't expect it to be that fast....
Send half of your thanks to Remi, I have stolen the idea of using the Numbers table from him (although he might have stolen it elsewhere
) and I wouldn't have had a chance to solve it before him had we been in the same time zone -
Thanx Jasper once again i am relieved......
Also i have one more query ..
I have an f_name column in one table . I have to match this column against f_name ,m_name,l_name. The where clause comes to f_name = f_name or f_name = m_name or f_name = l_name . this was taking a lot of time. I changed it to
select a.* from table a, table b where a.f_name = b.f_name union all select a.* from table a, table b where a.f_name = b.m_name and so on... In this way the time is reduced to half. If u can rewrite the query in a more optimal way... then pls do tell me.......
Also where r u from ? I am from New Delhi , India. Nice interacting with u. Thanx once again.
Rgds,
Rajesh
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply