SQL: how to get left 3 numbers from int

Question

I want to get the left 3 numbers from an integer that will be stored in a table. For example, if int is 1234567, I want to get 123. I want the second number (123) to also be int; I do not want to convert anything to a string.

(And yes, indeed, I have to work with strings, but I have no control over this aspect of the problem.)

Thanks!

Answer 1

For SQL Server, the easiest way :

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

Convert to a string, take the left most three characters and convert them back to INT.

Doing this purely on a numerical value becomes messy as you need to know how many digits you need to get rid of, etc ...

If you want to use only INT only, you will need to build something like this (at least you can do it in SQL Server - I'm not knowledgeable enough about Access to find out if this works in 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 this is always an approximation - what if you really have a really really large quantity ..... it can just fall through the cracks ....

Answer 2

Without casting to a string, how about this?

(T-sql)

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

Throws an error if int is less than 100, but it seems to work differently.

EDIT. To handle the error gracefully, you will have to use CASE since TSQL does not have a GREATEST () function ...

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

Answer 3

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

Edit: Infact I'm sure it will not care about cstr ().

Answer 4

 ;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 verify this, but he has to do the trick. Iterate until you finish with <1000, relying on integer division. You may need to filter out the first sentence to configure it.

For the original SQL Server 2005 SQL Server solution only

Answer 5

Well, if you have access to php, you can use substr

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

Convert integer to string in PHP

Good luck