TSQL 将大数重新映射为小数，但保留身份

2021-09-10 00:00:00 tsql sql-server

我正在尝试将一个巨大的数字加载到 Field1 INT 中，它只能容纳 max=2,147,483,647，根据它我不能改变 DDL，所以试图找到临时解决方案，从这个数字的中间切出一位数，然后添加唯一性检查.
这些数字的格式如下:29000001234，所以我的意思是保持这种格式中间有零以便于识别.我不想在这个任务中引入任何新的列/表，因为那里的自由有限，这是 3rd 方模式.

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.

任何人都可以提出更好的解决方案，如何重新映射/保持所有数字低于该限制；这是我的草稿:

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;

推荐答案

如果没有算术溢出或丢失原始数据，您就无法做到这一点.
如果目标表或查询的列数有限制，请使用多行:

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;

相关文章