Oracle正则表达式，如果修剪字符串只包含L或P字母

Question

我有以下列(lock_reason)，我想获得理想的输出列。逻辑:如果，除字母外的所有内容，结果字符串仅由L或P字符组成，则'X'否则为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

Answer 1

另一个选择是使用

的组合

• regexp_replace(删除所有不是字母的内容)和
• translate(去除剩余的P和L)。

剩下的很简单(case，对吧?)

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>

Answer 2

假设您想要删除所有非字母字符，然后检查剩余的字符串是否只有L或P字符，那么:

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

如果您的字符串由逗号分隔的术语组成，然后是一个可选的数字和一个字母字符，那么您可以使用:

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

对于样本数据:

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;

两输出:

<表类>LOCK_REASONDESIRED_OUTPUTtbody><<tr>2 p, QR3A, 2B, 7ZL, 9 pXPX7 a, 2 lP LX