I have the following column (lock_reason) and I would like to obtain the desired_output column. Logic: if, trimming everything but letters, the resulting string consists solely of L or P characters, then 'X' else NULL.

LOCK_REASON       DESIRED_OUTPUT
--------------------------------
2P, Q                       NULL
R                           NULL
3A, 2B, 7Z                  NULL
L, 9P                          X
P                              X
7A, 2L                      NULL
P, L                           X

CodePudding user response：

Assuming that you want to remove all non-alphabetical characters and then check if the remaining string is only L or P characters then:

SELECT lock_reason,
       CASE
       WHEN REGEXP_LIKE( REGEXP_REPLACE( lock_reason, '[^A-Z]' ), '^[LP] $' )
       THEN 'X'
       END AS desired_output
FROM   table_name;

If your string is composed of comma-space separated terms with an optional digit and then an alphabetic character then you could use:

SELECT lock_reason,
       CASE
       WHEN REGEXP_LIKE( lock_reason, '^(\d*[LP], )*\d*[LP]$' )
       THEN 'X'
       END AS desired_output
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name ( LOCK_REASON ) AS
SELECT '2P, Q'      FROM DUAL UNION ALL
SELECT 'R'          FROM DUAL UNION ALL
SELECT '3A, 2B, 7Z' FROM DUAL UNION ALL
SELECT 'L, 9P'      FROM DUAL UNION ALL
SELECT 'P'          FROM DUAL UNION ALL
SELECT '7A, 2L'     FROM DUAL UNION ALL
SELECT 'P, L'       FROM DUAL;

Both output:

LOCK_REASON	DESIRED_OUTPUT
2P, Q
R
3A, 2B, 7Z
L, 9P	X
P	X
7A, 2L
P, L	X

db<>fiddle here

CodePudding user response：

Another option is to use a combination of

• regexp_replace (to remove everything that isn't a letter) and
• translate (to remove P and L from what remained).

The rest is easy (case, right?).

SQL> select lock_reason,
  2    case when translate(regexp_replace(lock_reason, '[^[:alpha:]]', null), '#PL', '#') is null then 'X'
  3         else null
  4    end as result
  5  from test;

LOCK_REASON  RESULT
------------ ----------
2P, Q
R
3A, 2B, 7Z
L, 9P        X
P            X
7A, 2L
P, L         X

7 rows selected.

SQL>