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

well if you have access to php you could use substr

echo substr('1234567', 0, 3); and then convert the string back to an int

http://stackoverflow.com/questions/1035634/converting-int-to-string-in-php

good luck!

Answer 3

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

Answer 4

In access SELECT clng(left(cstr(field), 3)) FROM T should work.

Edit: Infact I bet it wont care about the cstr().

Answer 5

;WITH c10 AS
(
    SELECT
        Number
    FROM
        MyTable --?? WHERE Number>=1000
    UNION ALL
    SELECT Number/10 FROM c10 WHERE Number>=1000
)
SELECT Number FROM c10 WHERE Number < 1000

I can't test this, but it should do the trick. Iterate through until you end up with < 1000, relying on integer division. You may need to filter on the first clause to fine tune it

For a raw TSQL SQL Server 2005 solution only