SQL: how to get the left 3 numbers from an int

Question

I want to retrieve the left 3 numbers from an integer to be stored in a table. For example, if the int is 1234567, I want to retrieve 123. I want the second number (123) to also be an int; I don't want to convert anything to a string.

(And yes, really I should be working with strings. But I don't have control over that aspect of the issue.)

Thank you!

Answer 1

For SQL Server, the easiest way would definitely be:

SELECT CAST(LEFT(CAST(YourInt AS VARCHAR(100)), 3) AS INT)

Convert to string, take the left most three characters, and convert those back to an INT.

Doing it purely on the numerical value gets messy since you need to know how many digits you need to get rid of and so forth...

If you want to use purely only INT's, you'd have to construct something like this (at least you could do this in SQL Server - I'm not familiar enough with Access to know if that'll work in the Access SQL "dialect"):

DECLARE @MyInt INT = 1234567

SELECT
    CASE 
        WHEN @MyInt < 1000 THEN @MyInt
        WHEN @MyInt > 10000000 THEN @MyInt / 100000
        WHEN @MyInt > 1000000 THEN @MyInt / 10000
        WHEN @MyInt > 100000 THEN @MyInt / 1000
        WHEN @MyInt > 10000 THEN @MyInt / 100
        WHEN @MyInt > 1000 THEN @MyInt / 10
    END AS 'NewInt'

But that's always an approximation - what if you have a really really really large number..... it might just fall through the cracks....

Answer 2

Without casting to string, how about this?

(T-SQL)

select @i / power(10,floor(log10(@i))-2)

Throws an error if the int is less than 100, but seems to work otherwise.

EDIT: To handle the error gracefully, you'd have to use a CASE since TSQL has no GREATEST() function...

select @i / case when @i < 100 then 1 else power(10,floor(log10(@i))-2) end