SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Truncating numbers
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Makkaramestari
Starting Member

8 Posts

Posted - 02/20/2006 :  05:54:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  06:09:07  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/20/2006 :  06:14:46  Show Profile  Reply with Quote
Look up RIGHT in Books Online..
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  06:27:00  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

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

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  07:21:35  Show Profile  Reply with Quote
hehe..

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

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

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 02/20/2006 :  07:27:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  08:45:48  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 02/20/2006 :  09:31:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  10:54:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/20/2006 :  11:08:17  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 02/20/2006 :  12:34:50  Show Profile  Reply with Quote
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..

Edited by - RickD on 02/20/2006 12:36:12
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/20/2006 :  17:42:33  Show Profile  Reply with Quote
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 - 02/20/2006 :  17:46:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 02/20/2006 :  21:24:27  Show Profile  Visit jhermiz's Homepage  Reply with Quote
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 -- http://www.web-impulse.com

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

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/21/2006 :  03:39:28  Show Profile  Reply with Quote
@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
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 02/21/2006 :  13:09:38  Show Profile  Visit jhermiz's Homepage  Reply with Quote
You never mentioned anything about isolation. Don't take too much offense just pointing things out.




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

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

X002548
Not Just a Number

15586 Posts

Posted - 02/21/2006 :  16:18:55  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 02/21/2006 :  20:20:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1167 Posts

Posted - 02/22/2006 :  04:10:11  Show Profile  Reply with Quote
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 -- http://www.web-impulse.com

RS Blog -- http://weblogs.sqlteam.com/jhermiz



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)

USA
7020 Posts

Posted - 02/22/2006 :  11:20:11  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 02/22/2006 11:40:35
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000