SQL:如何从一个整数获取剩余的3个数字 [英] SQL: how to get the left 3 numbers from an int

问题描述

我想从一个整数中检索剩下的3个数字以存储在表中.例如，如果int是1234567，则我想检索123.我希望第二个数字(123)也可以是int.我不想将任何内容转换为字符串.

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

谢谢！

推荐答案

对于SQL Server，最简单的方式肯定是:

For SQL Server, the easiest way would definitely be:

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

转换为字符串，使用最左边的三个字符，然后将其转换回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...

如果只想使用INT，则必须构造类似的东西(至少您可以在SQL Server中做到这一点-我对Access并不很熟悉，知道它是否可以在Access中工作SQL方言"):

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

这篇关于SQL:如何从一个整数获取剩余的3个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！