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
 General SQL Server Forums
 New to SQL Server Programming
 Truncating numbers

Author  Topic 

Makkaramestari
Starting Member

8 Posts

Posted - 2006-02-20 : 05:54:53
Hi!

I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789

Makkaramestari

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 06:09:07
Here's a few quick options:

declare @Value dec(11,0)

set @Value = 12345678
select @Value,
right(cast(@Value as varchar),6) as Method1,
cast(@Value as bigint) % 1000000 as Method2

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-20 : 06:14:46
Look up RIGHT in Books Online..
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 06:27:00
Lol - or look at RIGHT in my example (Method 1)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-20 : 06:46:54
Sorry, my fault for not refreshing the page..
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 07:21:35
hehe..

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-20 : 07:27:45
quote:
Originally posted by Wanderer

hehe..

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!



oh btw, you don't really need to cast as it's implicit..
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 08:45:48
I got errors without the cast, I think because the base data type is dec.

declare @Value dec(11,0)

set @Value = 12345678
select @Value,
right(@Value ,6) as Method1,
(@Value ) % 1000000 as Method2

returns:

Server: Msg 206, Level 16, State 2, Line 4
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 4
Operand data type numeric is invalid for modulo operator.

on checking, those errors occurred when I didn't convert for the modulo issue. You are 100% correct - right 6 works on the dec(11,0)...



*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-20 : 09:31:29
Implicit conversion takes place if the datatype is int or bigint


declare @Value int
set @Value = 12345678
select @Value,
right(@Value ,6) as Method1,
(@Value ) % 1000000 as Method2


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 10:54:48
quote:
Originally posted by madhivanan


Implicit conversion takes place if the datatype is int or bigint
declare @Value int




Yes - but the original poster data is in a dec(11,0)
quote:
Originally posted by Makkaramestari

Hi!

I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789

Makkaramestari



hence I declared my @Value as dec(11,0) and worked from there.

I wonder which is more efficient:

right(@Value,6)
or
cast(@Value as bigint) % 1000000

I think the right reads easier, and seems to do less work, but then again, it returns char, whereas the poster wanted int:
quote:
eg. I want field2 int containing 234567 as result


I think I'll test the 2 using a function and stress it a bit.... I'll let you know what it looks like

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-20 : 11:08:17
hmm - looks like the right approach is using RIGHT (excuse the pun).

In a loop of 1000 exuctions of a function for RIGHT and a function using MODULO, the results were:
************** RIGHT FUNCTION Overall Duration: 123 ms ***************
************** MODULO FUNCTION Overall Duration: 313 ms ***************




*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-20 : 12:34:50
quote:
Originally posted by Wanderer

I got errors without the cast, I think because the base data type is dec.

declare @Value dec(11,0)

set @Value = 12345678
select @Value,
right(@Value ,6) as Method1,
(@Value ) % 1000000 as Method2

returns:

Server: Msg 206, Level 16, State 2, Line 4
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 4
Operand data type numeric is invalid for modulo operator.

on checking, those errors occurred when I didn't convert for the modulo issue. You are 100% correct - right 6 works on the dec(11,0)...



*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!



But,

declare @Value dec(11,0)

set @Value = 12345678
select @Value,
right(@Value ,6) as Method1

Will work without an explicit conversion..

You will need to cast (@Value ) % 1000000 as Method2 as the result from right will return a char..
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-20 : 17:42:33
OK

Time to bring in the normaliz(s)tion police.

First, the data should separated

Second, Problem solved




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-20 : 17:46:00
EDIT:

You're kidding, right?

I'm not paid enough.....



quote:
Originally posted by Makkaramestari

Hi!

I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789

Makkaramestari



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-02-20 : 21:24:27
quote:
Originally posted by Wanderer

hmm - looks like the right approach is using RIGHT (excuse the pun).

In a loop of 1000 exuctions of a function for RIGHT and a function using MODULO, the results were:
************** RIGHT FUNCTION Overall Duration: 123 ms ***************
************** MODULO FUNCTION Overall Duration: 313 ms ***************




*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!



Its great and all you tested, but a test like this isn't valid since there are factors that may contribute to a function to perform much slower.

Consider network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock, etc etc.




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-21 : 03:39:28
@Jhermiz:

In a test where the difference in the SQL is:

select dbo.@Right6(@Value)
or
select dbo.@Modulo6(@Value)

How does
quote:
network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock
affect the DIFFERENCE between those 2 execution times when considered in isolation?

I totally agree that differing conditions will cause different executions times per execution, but since the calling code would, at a point in time, either call the @Right6 function, or the @Modulo6 function, those factors can be ignored for the purpose of considering the performance of the 2 different function, since they would be the same or either call, depending on which was used.

I did flush buffers before and between executions, to ensure fair comparisons.

Please bear in mind - my numbers are not trying to predict the performance on the Poster's systems , as hardware, configurations, and all the factors you mentioned would make that impossible. What I was doing was testing the 2 functions, in isolation, to determine which was superior.

Am I missing something? Also, sorry for the long-winded response - hope it makes some kind of sense....

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

jhermiz

3564 Posts

Posted - 2006-02-21 : 13:09:38
You never mentioned anything about isolation. Don't take too much offense just pointing things out.




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-21 : 16:18:55
Considering that the conversation is about apples, discussing oranges won't bear any fruit



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-21 : 20:20:36
I tested the two methods with a table containg 1 million rows. The Modulo method was faster every time I ran the test.

Here's the code I used to load the table and test the methods, and some typical results.

Function f_table_number_range is available in the script library.

/*
drop table #t
create table #t ( num decimal(11,0) not null )
*/
/*
truncate table #t
-- Load table with one million rows
insert into #t
select
number + 1234567.
from
dbo.f_table_number_range(1,1000000)
*/
go
declare @out varchar(20)
declare @start datetime
declare @end datetime
select @start =getdate()

select @out =max(right(num ,6)) from #t


select @end =getdate()
select [MS Right] = datediff(ms,@start,@end)

go
declare @out varchar(20)
declare @start datetime
declare @end datetime
select @start =getdate()

select @out = max(convert(bigint,num)% 1000000) from #t


select @end =getdate()
select [MS Modulo] = datediff(ms,@start,@end)



Results:

MS Right
-----------
1966

(1 row(s) affected)

MS Modulo
-----------
843

(1 row(s) affected)



CODO ERGO SUM
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-22 : 04:10:11
quote:
Originally posted by jhermiz

You never mentioned anything about isolation. Don't take too much offense just pointing things out.


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]



No offence taken at all - sorry if I came over that way

The reason I "tested in isolation" was because in the final code, only 1 method is going to be used, so the environmental factors don't affect the decision between which method to choose.

... hmm - I don't seem to be able to find a good way to explain why I tested like this.

Basically, I tested "in isolation" because none of the factors mentioned will have an affect on the relative performance of the 2 methods - i.e they are both SQL function calls, using built in SQL function - neither of them are any more, or less, reliant on "network bandwidth, lantency, traffic, current processes, current schedule jobs, hardware, server usage, cpu clock".

Hope that explains my thinking a bit better... - I am very keen to have flaws in my approach pointed out, and explained, so I can improve.

@Michael - really stuggling to find that function - search resources gives no articles. Care to give the code, or a direct link for this poor fool? (fool-Me, btw).

I notice you don't clean buffers between your executions - that should have an accfect, imho. What happens when you run modulo6 first, and not right6?

I wonder if the difference is because of the fact I created them as functions - I'll try and check that.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-22 : 11:20:11
quote:
Originally posted by Wanderer
...@Michael - really stuggling to find that function - search resources gives no articles. Care to give the code, or a direct link for this poor fool? (fool-Me, btw).

I notice you don't clean buffers between your executions - that should have an accfect, imho. What happens when you run modulo6 first, and not right6?...


The F_TABLE_NUMBER_RANGE function is available in this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Edit: I have found it easier to use Google to search SQLTeam. When I put F_TABLE_NUMBER_RANGE into a Google search, the link above was the first item.

I did not clean the buffers because it seemed unnecessary and undesirable. I wanted the table data cached so that I was not testing the IO of the system, just the difference in the query run-time. The system I ran the test on was a fast test system that was idle at the time I ran it, so there was no other activity.

I ran both queries many times after I loaded the temp table, and none of the runs had any significant difference in run-time. I ran them in different order, and run each one repeatedly by itself. Same result each time.

I always try to post my test scripts so you can try it youself. No need to take my word for it.







CODO ERGO SUM
Go to Top of Page
    Next Page

- Advertisement -