[20240928]10,16进制转10,16进制的sql脚本.txt

--//朋友建议提的建议，x可以放在前面或者后面表示16进制，放在后面的好处不用移动到前面输入x，修改完善代码。
$ cat hd.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name:        hd.sql
-- Purpose:     display hex or dec to hex or dec
--
-- Author:      lfree
-- Usage:
--     @ hd <hex|dec>
--
--------------------------------------------------------------------------------

define vc=&1

column text format a20
column hex_status format a10
SELECT text
      ,n10
      ,REPLACE (TO_CHAR (n10, LPAD ('x', 16, 'x')), ' ') c16
      ,hex_status
  FROM (SELECT '&&vc' text
--             ,DECODE ( hex_status ,'hex', TO_NUMBER ( SUBSTR ('&&vc', INSTR (LOWER ('&&vc'), 'x',-1) + 1) ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,DECODE ( hex_status ,'hex', TO_NUMBER ( replace (lower('&&vc'), 'x') ,'xxxxxxxxxxxxxxxx') ,'dec', TO_NUMBER ('&&vc') ,0) n10
              ,hex_status
          FROM (SELECT CASE
                          WHEN REGEXP_LIKE (LOWER ('&&vc'), '^[0-9]+$') THEN 'dec'
                          WHEN REGEXP_LIKE (LOWER ('&&vc') ,'^0?[xX]?[0-9a-f]+[xX]?$') THEN 'hex'
                          ELSE 'other'
                       END AS Hex_Status
                  FROM DUAL));

--//测试代码如下：
SCOTT@book01p> @ hd x12
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
x12                                     18 12                hex

SCOTT@book01p> @ hd 12x
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
12x                                     18 12                hex

SCOTT@book01p> @ hd 0x12x
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
0x12x                                   18 12                hex

SCOTT@book01p> @ hd x12x
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
x12x                                    18 12                hex

SCOTT@book01p> @ hd x12
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
x12                                     18 12                hex

SCOTT@book01p> @ hd x12x12
TEXT                                   N10 C16               HEX_STATUS
-------------------- --------------------- ----------------- ----------
x12x12                                   0 0                 other