您可以使用CONNECT BY和REGEXP_SUBSTR如下：

SQL> WITH YOUR_DATA AS (
  2      SELECT 'PAN~HLASD4564P|VOTER_ID~VDD3455355' AS STR
  3      FROM DUAL
  4  ) -- Your query starts from here
  5  SELECT 
  6      REGEXP_SUBSTR(NEW_STR, '[^~]+', 1, 1) AS ID,
  7      REGEXP_SUBSTR(NEW_STR, '[^~]+', 1, 2) AS VALUE
  8  FROM
  9      (
 10          SELECT REGEXP_SUBSTR(STR, '[^|]+', 1, LEVEL) NEW_STR
 11          FROM YOUR_DATA
 12          CONNECT BY LEVEL <= 2
 13      );

ID         VALUE
---------- -------------
PAN        HLASD4564P
VOTER_ID   VDD3455355

SQL>

WITH your_data AS (
    SELECT
        'ISAT_FWASSAMBA_008' AS str
    FROM
        dual
) -- Your query starts from here
SELECT
    regexp_substr(new_str, '[^_]+', 1, 1) AS str1,
    regexp_substr(new_str, '[^_]+', 1, 2) AS str2,
    regexp_substr(new_str, '[^_]+', 1, 3) AS str3,
    regexp_substr(new_str, '[^_]+', 1, 4) AS str4
FROM
    (
        SELECT
            regexp_substr(str, '[^|]+', 1, level) new_str
        FROM
            your_data
        CONNECT BY level <= 1
    );

运转良好