TSQL将大数字重映射为小，但要保持身份。

I'm trying to load a huge number into Field1 INT which can hold only max=2,147,483,647, according to it I can't change DDL, so tried to find adhoc solution to cut out single digit from the middle of this number and then add check for uniqueness.
This numbers are in the format like: 29000001234, so I mean to keep this format with zeros in the middle to easy recognizing. I don't want to introduce any new columns/tables into this task, as limited in freedom there, this is 3rd party schema.

我正在尝试将一个巨大的数字加载到Field1 INT中，该INT只能保存max=2,147,483,647，根据它我无法改变DDL，所以我尝试寻找一种特殊的解决方案，从这个数字的中间删除一个数字，然后添加check for unique。这些数字的格式是:29000001234，所以我的意思是保持这种格式，中间的0便于识别。我不想在这个任务中引入任何新的列/表，因为这里的*度有限，这是第三方模式。

Can anybody suggest better solution, how to remap/keep all numbers under that limit; this is my draft:

有人能提出更好的解决方案吗，如何重新映射/保持所有的数字在那个范围内?这是我的草案:

DECLARE @fl FLOAT = 29000001234
DECLARE @I  INT

SELECT  @i = (SUBSTRING(CAST(CAST(@fl AS BIGINT) AS VARCHAR(18)),1,4) +  
          SUBSTRING(CAST(CAST(@fl AS BIGINT) AS VARCHAR(18)),7,LEN(CAST(CAST(@fl AS BIGINT) AS VARCHAR(18))))  )
select @i;

5 个解决方案

#1

But if you really want to remove the middle digits, here's another approach:

但是如果你真的想去掉中间的数字，这里有另一种方法:

DECLARE @fl FLOAT = 29000001234
DECLARE @I  INT

DECLARE @StringFloat as varchar(80)
SET @StringFloat = CONVERT(varchar(80), CAST(@fl AS bigint))
SET @I = CAST( CONCAT(LEFT( @StringFloat, 4 ), RIGHT( @StringFloat, 5 )) as int )

SELECT @i;

#2

I think arithmetic operations should be less expensive than string operations, so you should use them instead:

我认为算术运算应该比字符串运算便宜一些，所以你应该用它们来代替:

DECLARE @fl FLOAT = 29000001234 
DECLARE @flBig BIGINT = @fl
DECLARE @i  INT

SET @i = (@flBig / 1000000000) * 10000000 + (@flBig % 100000000)
select @i;  --> 290001234

Provided example assumes the first part of the number will have a maximum of two digits (i.e. 29 in your case) and that you want to allow larger number in the left part (up to 999999).

假设数字的第一部分最多有两个数字(例如，在你的例子中是29)，并且你希望在左边的部分允许更大的数字(高达99999999)。

NOTE: parentheses are redundant, as division and multiplication have the same priority and modulo operator has higher precedence over addition. I have used them just to highlight the parts of the computation.

注意:圆括号是冗余的，因为除法和乘法具有相同的优先级，而且模运算符比加法优先级更高。我用它们来突出计算的部分。

#3

You can't do that without any arithmetic overflow, or with out losing your original data.
If you have a limitation in columns of your destination table or query, use multiple rows:

如果没有算术溢出，或者失去原始数据，就不能这样做。如果目标表或查询的列有限制，请使用多行:

declare @c bigint = 29000001234;
declare @s bigint = 1000000000;    -- Separator value

;with cte(partNo, partValue) as (
    select 1, @c % @s
    union all
    select partNo + 1, (@c / power(@s, partNo)) % @s
    from cte
    where (@c / power(@s, partNo)) > 0
)
select partValue
from cte;

#4

Seems like a strange situation, not sure why you need to go to all the trouble of converting a big number to a string and then randomly remove a digit. Some more information about why or what the real goal is would be helpful.

看起来很奇怪，不知道为什么要把一个大数字转换成字符串，然后随机删除一个数字。关于为什么或真正的目标是什么的更多信息将会有帮助。

That said, maybe it would be easier to just subtract a constant amount from these values? e.g.:

也就是说，也许从这些值中减去一个常数会更简单?例如:

DECLARE @fl FLOAT = 29000001234
DECLARE @I INT
DECLARE @OFFSET BIGINT = 29000000000
SET @I = CAST(@fl AS BIGINT)-@OFFSET
SELECT @I

Which gives you an INT of 1234 as the result using your example.

使用示例，将得到一个1234的INT值。

#5

The following creation drops increasingly wide blocks of digits from the original third party value and returns the results that fit in an INT. The results could be outer joined with the existing data to find a suitable new value.

下面的创建从原始的第三方值中删除越来越宽的数字块，并返回适合于INT的结果。

declare @ThirdPartyValue as BigInt = 29000001234;
declare @MaxInt as BigInt = 2147483647;

declare @TPV as VarChar(19) = Cast( @ThirdPartyValue as VarChar(19) );
declare @TPVLen as Int = Len( @TPV );

with
  -- 0 through 9.
  Digits as (
    select Digit from ( values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) as Digits( Digit ) ),
  -- 0 through   @TPVLen .
  Positions as (
    select Ten_1.Digit * 10 + Ten_0.Digit as Number
      from Digits as Ten_0 cross join Digits as Ten_1
      where Ten_1.Digit * 10 + Ten_0.Digit <= @TPVLen ),
  -- 1 through   @TPVLen - 1 .
  Widths as (
    select Number
      from Positions
      where 0 < Number and Number < @TPVLen ),
  -- Try dropping   Width   digits at   Position   from   @TPV .
  AlteredTPVs as (
    select P.Number as Position, W.Number as Width,
      Stuff( @TPV, P.Number, W.Number, '' ) as AlteredTPV
      from Positions as P cross join Widths as W
      where P.Number + W.Number <= @TPVLen )
  -- See which results fit in an   Int .
  select Position, Width, AlteredTPV, Cast( AlteredTPV as BigInt ) as AlteredTPVBigInt
    from AlteredTPVs
    where Cast( AlteredTPV as BigInt ) <= @MaxInt -- Comment out this line to see all results.
    order by Width, Position

It could be more clever about returning only distinct new values.

只返回不同的新值可能更聪明。

This general idea could be used to hunt down blocks of zeroes or other suitable patterns to arrive at a set of values to be tested against the existing data.

这个一般的想法可以用来搜索0块或其他合适的模式，从而得到一组要针对现有数据进行测试的值。

#1