sql server 2000 finding the decimal value of a number 2

[duchovnick]

Hi !
I'd like to know the decimal value of my number. Say, if i have a "26" i'm looking for the function to return "2".
Thanks !

 

[AlexCuse]

What do you mean by decimal value? If you mean simply the number in the tens position, and you don't have any decimal places to worry about, try this:

[COLOR=blue]declare[/color] @num [COLOR=blue]integer[/color]

[COLOR=blue]set[/color] @num = 26

[COLOR=blue]select[/color] @num/10

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[duchovnick]

Thanks a lot !

 

[AlexCuse]

Of course, if you want ONLY the value of the number in the tens position, you would need to cast as varchar, take the right two characters, cast back to int, and then divided by ten. What would you want to show for say, 1024?

If your answer is 102, that will work fine. If you want only the two, try this:

[COLOR=blue]declare[/color] @num [COLOR=blue]integer[/color]

[COLOR=blue]set[/color] @num = 1024

[COLOR=blue]select[/color] @num/10

[COLOR=blue]select[/color] [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]right[/color]([COLOR=#FF00FF]cast[/color](@num [COLOR=blue]as[/color] [COLOR=blue]varchar[/color]), 2) [COLOR=blue]as[/color] [COLOR=blue]int[/color])/10

[/code]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

>> If you want only the two, try this:

There's another way to accomplish this while keeping your values as numbers. Converting to varchar is fine, but the performance won't be as good as if you keep the values as integers.

Declare @Value int
Declare @Digit tinyint

Set @Value = 1024
Set @Digit = 2

Select @Value / power(10,@Digit -1) % 10

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

George - I hope I never need to use that technique, but its' pretty slick

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Yeah.

I was just goofing around with it. Both our queries give the same results if the value is positive. My query will give you a negative number for each digit in a number, whereas yours will return a positive number.

Also, with about 100,000 records, my query is only about 50 milliseconds faster than yours.

I still think that if you start with a number and want to end up with a number, that you are better off keeping it a number. I suppose it doesn't really matter though.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I would just use your version, and take Abs() of it if I needed a positive digit. Unnecessary casts are the worst!

Ignorance of certain subjects is a great part of wisdom

 

[tempo1]

Thanks a lot !
In fact i searched the "mod()" function but as i see it doesnt exist in t-sql so i'l use your techniques and thank you again.

 

[earthandfire]

tempo1, the "mod()" function does exist as in George's example:

[tt] @Value / power(10,@Digit -1) % 10 [/tt]

which means:

@Value divided by 10[sup]@Digit - 1[/sup] MOD 10


Hope this helps.