Exclude numeric values from report

Question

I have a field named statu in the table TB_ORDERS.

If the field status has a numeric value, I should exclude that record from report - only alphabetical should be shown.

How do I do that?

Answer 1

I should exclude that record from report - only alphabetical should be shown.

You could use REGEXP_LIKE in the filter predicate.

• NOT REGEXP_LIKE(status, '[[:digit:]]+')

Or,

• NOT REGEXP_LIKE(status, '\d+')

Or,

• NOT REGEXP_LIKE(status, '[0-9]+')

For example,

SELECT * FROM TB_ORDERS
WHERE NOT REGEXP_LIKE(status, '[[:digit:]]+');

Demo

SQL> WITH TB_ORDERS AS(
  2  SELECT 'abc123abc' status FROM dual UNION ALL
  3  SELECT 'abcabc' status FROM dual UNION ALL
  4  SELECT 'abcabc123' status FROM dual UNION ALL
  5  SELECT '123abcabc123' status FROM dual
  6  )
  7  SELECT * FROM TB_ORDERS
  8  WHERE NOT REGEXP_LIKE(status, '[[:digit:]]+');

STATUS
------------
abcabc

SQL>

Or,

SQL> WITH TB_ORDERS AS(
  2  SELECT 'abc123abc' status FROM dual UNION ALL
  3  SELECT 'abcabc' status FROM dual UNION ALL
  4  SELECT 'abcabc123' status FROM dual UNION ALL
  5  SELECT '123abcabc123' status FROM dual
  6  )
  7  SELECT * FROM TB_ORDERS
  8  WHERE NOT REGEXP_LIKE(status, '\d+');

STATUS
------------
abcabc

SQL>

Or,

SQL> WITH TB_ORDERS AS(
  2  SELECT 'abc123abc' status FROM dual UNION ALL
  3  SELECT 'abcabc' status FROM dual UNION ALL
  4  SELECT 'abcabc123' status FROM dual UNION ALL
  5  SELECT '123abcabc123' status FROM dual
  6  )
  7  SELECT * FROM TB_ORDERS
  8  WHERE NOT REGEXP_LIKE(status, '[0-9]+');

STATUS
------------
abcabc

SQL>

Update On OP's request

SQL> WITH TB_ORDERS AS(
  2  SELECT '*abc123abc' status FROM dual UNION ALL
  3  SELECT 'ab*cabc' status FROM dual UNION ALL
  4  SELECT '****' status FROM dual UNION ALL
  5  SELECT '123abcabc123' status FROM dual
  6  )
  7  SELECT * FROM TB_ORDERS
  8  WHERE NOT REGEXP_LIKE(status, '[0-9]+');

STATUS
------------
ab*cabc
****

SQL>