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 |
