Refactor Oracle SQL query

Question

Example table (IP)

ID      IP_NO               PHONE   
-----   --------            ---------
101     192.205.230.70      +535950331
10#     192.205a.230.70     +672819233
103     192.205.230.72      +991873112
10!     192.205.230.78      +764616233

Query which I am using right now:

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

Result table of above queries:

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

Result table which I want:

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

Is it possible to transform above queries to see invalid characters in the result table?

Answer 1

You can try the following query to achieve the desired result.

I have used connect by to convert the row data into the column.

SELECT
    'IP' AS TABLE_,
    CASE LVL.DUMMY
        WHEN 1   THEN NEW_ID
        WHEN 2   THEN NEW_IP_NO
        WHEN 3   THEN NEW_PHONE
    END AS INVALID_CHAR,
    CASE LVL.DUMMY
        WHEN 1   THEN 'mt.id'
        WHEN 2   THEN 'mt.ip_no'
        WHEN 3   THEN 'mt.phone'
    END AS COLUMN_
FROM
    ( SELECT
            ID, REGEXP_REPLACE(ID, '[0-9]', '') NEW_ID,
            IP_NO, REGEXP_REPLACE(IP_NO, '[0-9.]', '') NEW_IP_NO,
            PHONE, REGEXP_REPLACE(PHONE, '[0-9+]', '') NEW_PHONE
        FROM
            YOUR_DATA
    )
    JOIN (
        SELECT
            LEVEL   AS DUMMY
        FROM
            DUAL
        CONNECT BY
            LEVEL <= 3
    ) LVL ON ( 1 = 1 )
WHERE
    CASE LVL.DUMMY
        WHEN 1   THEN NEW_ID
        WHEN 2   THEN NEW_IP_NO
        WHEN 3   THEN NEW_PHONE
    END IS NOT NULL;

db<>fiddle demo

Cheers!!

Answer 2

This should work

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