將任意字元串原始轉換為整數

我在表中有非數字字元串 ID，並希望將它們轉換為唯一整數。我需要映射是可逆的/一對一的。字元串 ID 具有統一的長度。

我想通過從字面上聚合 ID 中每個字元的 ASCII 碼來做到這一點。該ASCII函式只返回最左邊的字元。有沒有一種有效的方法來投射整個字元串？

假設您使用的是尚未確認的 SQL Server，我將使用內聯表值函式 (TVF)，它可用於將字元數據有效且安全地轉換為整數值，用於 8 字元串個字元或更少。該字元串的最大長度必須為 8 個字元，因為這是 SQL Server 中整數的最大字節數。

這是 TVF：

IF OBJECT_ID('dbo.GetBigInt') IS NOT NULL
DROP FUNCTION dbo.GetBigInt;
GO
CREATE FUNCTION dbo.GetBigInt (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BigIntResult = CASE WHEN LEN(@data) > 8 THEN 1/0 
   ELSE CONVERT(BIGINT, CONVERT(VARBINARY(8), @data, 0)) END;
GO

為了測試該功能，我創建了以下帶有一些範例行的測試台表：

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
   SomeText VARCHAR(100)
);

INSERT INTO #t (SomeText)
VALUES ('aaaaaaaa')
   , ('AAAAAAAA')
   , ('aaaaaaab')
   , ('ZZZZZZZZ')
   , ('ÿÿÿÿÿÿÿÿ')
   , (CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1))
   , (CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127))
   , (CHAR(128) + CHAR(127) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128))
   , ('aaaaaaaba');

像這樣對桌子執行 TVF：

SELECT *
FROM #t t
CROSS APPLY dbo.GetBigInt(t.SomeText) g;

範例中的最後一行有意包含 9 個字元，無法可靠地將其轉換為 8 字節整數。當 TVF 嘗試轉換該行時，它將顯示以下錯誤：

消息 8134，級別 16，狀態 1，第 32 行

遇到除以零錯誤。

樣本數據中其他行的結果是：

為了測試 TVF 的速度，我在表中插入了 1,000,000 行，如下所示：

INSERT INTO #t (SomeText)
SELECT TOP(1000000) LEFT(c1.name, 8)
FROM sys.columns c1
   CROSS JOIN sys.columns c2
   CROSS JOIN sys.columns c3;

針對 1,000,000 行執行的查詢的執行計劃：

如果您的“非數字字元串 ID”超過 8 個字節，我建議您將它們簡單地轉換為BINARY(x)或VARBINARY(x)值，x然後是字元數據的最大長度。

IF OBJECT_ID('dbo.GetBinary') IS NOT NULL
DROP FUNCTION dbo.GetBinary;
GO
CREATE FUNCTION dbo.GetBinary (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BinaryResult = CONVERT(VARBINARY(1000), @data, 0);
GO

SELECT *
FROM #t t
CROSS APPLY dbo.GetBinary(t.SomeText) g;

它必須是整數嗎？否則十六進制可能會起作用。例子：

values hex('a12babab')"

1               
----------------
6131326261626162

如果您需要來自 String -> Int 的雙射，則 String 不能太長

引用自：https://dba.stackexchange.com/questions/147928