1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
CREATE FUNCTION BIGINT2CHAR6(
@Nombre AS BIGINT
)
RETURNS CHAR(6)
BEGIN
DECLARE @tmp VARCHAR(12) = ''
;WITH Nb(n, char6, tmp,reste) AS (
SELECT 5, CAST(CHAR(@Nombre / POWER(CAST(255 AS BIGINT), 6)) AS VARCHAR(6)),@Nombre / POWER(CAST(255 AS BIGINT), 6), @Nombre - (@Nombre / POWER(CAST(255 AS BIGINT), 6)) * POWER(CAST(255 AS BIGINT), 6)
UNION ALL
SELECT n - 1, CAST(CHAR(reste / POWER(CAST(255 AS BIGINT), n)) AS VARCHAR(6)),reste / POWER(CAST(255 AS BIGINT), n), reste - (reste / POWER(CAST(255 AS BIGINT), n)) * POWER(CAST(255 AS BIGINT), n)
FROM Nb
WHERE n + 1 > 0
)
SELECT @tmp += char6
FROM Nb
WHERE tmp <> 0
RETURN CAST(@tmp AS CHAR(6))
END
GO
CREATE FUNCTION CHAR62BIGINT(
@char6 CHAR(6)
)
RETURNS BIGINT
AS
BEGIN
DECLARE @BigInt BIGINT
;WITH Nb(n) AS (
SELECT LEN(@char6)
UNION ALL
SELECT n - 1
FROM Nb
WHERE n > 0
)
SELECT @BigInt = SUM(ASCII(SUBSTRING(@char6, n, 1)) * POWER(CAST(255 AS BIGINT), LEN(@char6) - n))
FROM Nb
RETURN @BigInt
END
GO
;WITH Test(nombre) AS (
SELECT 4568461321
union all
select 98456542313
union all
select 1345886594
union all
select 12345678998
)
SELECT
nombre AS original,
dbo.BIGINT2CHAR6(Nombre) as CHAR6,
dbo.CHAR62BIGINT(dbo.BIGINT2CHAR6(Nombre)) as DoubleConversion
FROM test |
Partager