7

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
dmr
  • 21,811
  • 37
  • 100
  • 138

5 Answers5

17

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....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    I gotta learn to type faster. had this all queued up to post SELECT (CAST(LEFT(CAST(1234567 AS VARCHAR), 3) AS INT)) :) –  Apr 14 '10 at 18:45
  • Does LEFT() have trouble if the string's length is shorter than the requested length? If so, you'll want to prepend "000" to the string before taking the LEFT() of it. – Carl Manaster Apr 14 '10 at 19:57
  • None of these will work in Access, except possibly as passthrough, but that only works when the it returns a recordset. – David-W-Fenton Apr 15 '10 at 20:08
  • Note that the LEFT() method would need to be expanded if it has to account for negative integers. – Bacon Bits Jan 17 '20 at 14:50
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
JC Ford
  • 6,946
  • 3
  • 25
  • 34
  • While a string-casting form probably performs better, I always prefer math-based solutions to these kinds of problems. – Philip Kelley Apr 14 '10 at 19:45
  • Jet/ACE's SQL doesn't need to coerce the number to a string, as it's Left() function implicitly does so. You do may or may not need to coerce the string result back to a number, depending on what you're using it for. I just can't see why one would avoid the simpler approach. – David-W-Fenton Apr 16 '10 at 18:31
1

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

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

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Er, why cast a 3-digit number as a long? CInt() will do just fine for 3-digit numbers. The CStr() is not necessary, because Left() implicitly coerces the numeric value to a string for processing. I don't like relying on implicit coercion, but depending on the implicit coercion avoids an extra function call for each row, and if you're doing this in a query that returns a large resultset, that could be a significant performance issue. But I don't like it, nonetheless. – David-W-Fenton Apr 15 '10 at 20:11
  • Well, if your going to cast to an integer may as well use a 32bit one – Alex K. Apr 15 '10 at 20:59
  • Uh, why? I don't know that it makes a difference, but we don't know the context in which it's being used. Using CLng() for values that you know are CInt() violates all principles of strong data typing that I'm aware of. You could also use Val() in Access, though not via OLEDB/ODBC. But I expect that's less efficient than either CLng() or CInt(). – David-W-Fenton Apr 16 '10 at 18:33
1
;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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • You posted this after the answer had already been edited with tags that indicate that it's being run in Access, so for your answer to be worthwhile, you need to explain how T-SQL is relevant to an Access question. – David-W-Fenton Apr 15 '10 at 20:12
  • @David-W-Fenton: "I am working in MS Access with tables linked from SQl Server" -> so use a pass through query rather than using Jet-SQL. Does he want the 1st 3 numbers or not? Not in Jet SQL without using strings... – gbn Apr 16 '10 at 04:46
  • @gbn: er, what? Of course it can be done in Jet/ACE SQL with no need whatsoever for a passthrough. Why you think it's important to avoid built-in functions like Left() and CLng() that are supported by Jet/ACE SQL in all contexts is beyond me. Also, note that passthroughs have their limitations, depending on what they are used for. – David-W-Fenton Apr 16 '10 at 18:29
  • @David-W-Fenton: OP said "I don't want to convert anything to a string." Did you read the question? And where's your answer if it's so easy... – gbn Apr 16 '10 at 18:33
  • @gbn: it's pretty clear from context that when the OP says "I don't want to convert anything to a string" he means for storage, which is made clear by the parenthetical statement "And yes, really I should be working with strings. But I don't have control over that aspect of the issue.", which clearly indicates that the problem is not conversion to strings to get the proper result, but either storage or processing of the result. – David-W-Fenton Apr 17 '10 at 21:42
  • @gbn: "where's your answer if it's so easy" -- why would I post a redundant answer? That is, the possibilities have already been covered. I have commented on those answers where they seemed incomplete or misleading. Would you suggest that I post an answer that duplicates what's already been suggested by someone else? – David-W-Fenton Apr 17 '10 at 21:43
  • @David-W-Fenton: there is no answer for Access only that avoids the use of strings. And you've been critical of every answer. – gbn Apr 18 '10 at 08:43
  • I have never criticized the use of strings. I've only pointed out the minor inaccuracies in the answers that use strings. Go back and read my comments and you'll see that this is true. – David-W-Fenton Apr 18 '10 at 22:36
0

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

Converting an integer to a string in PHP

good luck!

Community
  • 1
  • 1
Doug Molineux
  • 12,283
  • 25
  • 92
  • 144