16進数から10進数の変換
Posted feedbacks - SQL
bleis-tift
#9067()
[
SQL
]
SQL Server 2008 で確認しました。 最大値は、bigint の限界なので、9,223,372,036,854,775,807 です。
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 | WITH
InputStrs(id, hex) AS (
SELECT 1, '0x12437308CCB6'
UNION ALL
SELECT 2, '0x2C9C1227FC6520B'
)
, HexStrs(id, hex) AS (
SELECT
id
, SUBSTRING(hex, 3, LEN(hex))
FROM
InputStrs
)
, MaxLen(max_len) AS (
SELECT MAX(LEN(hex)) FROM HexStrs
)
, Seq(id, n) AS (
SELECT id, 1 FROM HexStrs
UNION ALL
SELECT
Seq.id
, n + 1
FROM
Seq INNER JOIN HexStrs ON Seq.id = HexStrs.id
WHERE
n + 1 <= (SELECT max_len FROM MaxLen)
)
, Chs(id, ch, i) AS (
SELECT
id
, SUBSTRING(hex, LEN(hex), 1)
, 1
FROM
HexStrs
UNION ALL
SELECT
Chs.id
, SUBSTRING(hex, LEN(hex) - Chs.i, 1)
, i + 1
FROM
Chs
INNER JOIN HexStrs ON Chs.id = HexStrs.id
WHERE
i < (SELECT max_len FROM MaxLen)
)
SELECT
SUM(CASE
WHEN ch = ''
THEN 0
WHEN ch LIKE '[A-F]'
THEN ASCII(ch) - ASCII('A') + 10
ELSE CAST(ch AS int)
END * POWER(CAST(16 AS bigint), i - 1))
FROM
Chs
GROUP BY
id
|
shojiHIDAKA #8955() Rating1/1=1.00
１６進数を１０進数に変換してください。
ただし、入出力は文字列とし、次の変換は最低必ずできなければいけないこととします。
2.0x2C9C1227FC6520B →200904012311450123
あわせて、扱える最大の整数も明らかにしてください。
[ reply ]