sql - 重构 Oracle SQL 查询

Question

示例表 (IP)

ID      IP_NO               PHONE   
-----   --------            ---------
101     192.205.230.70      [phone]
10#     192.205a.230.70     [phone]
103     192.205.230.72      [phone]
10!     192.205.230.78      [phone]

我现在正在使用的查询：

SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.ID' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.id, '0123456789', ' '))) > 0
OR ip.id IS NULL
UNION
SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.ip_no' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.ip_no, '0123456789.', ' '))) > 0
OR ip.ip_no IS NULL
UNION
SELECT 'IP' as TABLE, COUNT(*) AS ERROR_COUNT, 'mt.phone' AS COLUMN FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.phone, '+0123456789', ' '))) > 0
OR ip.phone IS NULL

上述查询的结果表：

TABLE       ERROR_COUNT     COLUMN 
-----       -----------     ------
IP          2               mt.id
IP          1               mt.ip_no
IP          0               mt.phone

我想要的结果表：

TABLE       INVALID_CHAR    COLUMN 
-----       -----------     ------
IP          #               mt.id
IP          !               mt.id
IP          a               mt.ip_no

是否可以转换上述查询以查看结果表中的无效字符？

标签： sqloracle

Answer 1

这应该工作

SELECT 'IP' as t, TRIM(TRANSLATE(ip.id, '0123456789', ' ')) AS INVALID_CHAR, 'mt.ID' AS c FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.id, '0123456789', ' '))) > 0
UNION
SELECT 'IP' as t, TRIM(TRANSLATE(ip.ip_no, '0123456789.', ' ')) AS INVALID_CHAR, 'mt.ip_no' AS c FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.ip_no, '0123456789.', ' '))) > 0
UNION
SELECT 'IP' as t, TRIM(TRANSLATE(ip.phone, '+0123456789', ' ')) AS INVALID_CHAR, 'mt.phone' AS c FROM ip
WHERE LENGTH(TRIM(TRANSLATE(ip.phone, '+0123456789', ' '))) > 0