問題描述

我正在嘗試將一個巨大的數(shù)字加載到 Field1 INT 中，它只能容納 max=2,147,483,647，根據(jù)它我不能改變 DDL，所以試圖找到臨時解決方案，從這個數(shù)字的中間切出一位數(shù)，然后添加唯一性檢查.
這些數(shù)字的格式如下:29000001234，所以我的意思是保持這種格式中間有零以便于識別.我不想在這個任務(wù)中引入任何新的列/表，因?yàn)槟抢锏淖杂捎邢蓿@是 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.

任何人都可以提出更好的解決方案，如何重新映射/保持所有數(shù)字低于該限制；這是我的草稿:

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;

推薦答案

如果沒有算術(shù)溢出或丟失原始數(shù)據(jù)，您就無法做到這一點(diǎn).
如果目標(biāo)表或查詢的列數(shù)有限制，請使用多行:

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;

這篇關(guān)于TSQL 將大數(shù)重新映射為小數(shù)，但保留身份的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！