Isolate null value with REGEXP_LIKE

Question

I need to isolate a string result which contains a null value. The idea is to recognize when a string sentence contains a value that is null.

WITH test_data (text_value) AS (
  SELECT 
    'null,test' 
  FROM 
    DUAL 
  UNION ALL 
  SELECT 
    '12345678' 
  FROM 
    DUAL
) 
SELECT 
  td.text_value, 
  CASE WHEN REGEXP_LIKE(NULL, '.') THEN 'Y' ELSE 'N' END AS VALID 
   FROM 
     test_data td

The expected result is to isolate a result that has a null value

null,test N

Answer 1

You could eschew invoking regular expressions entirely here, and just use a LIKE trick:

SELECT
    td.text_value,
    CASE WHEN ',' || td.text_value || ',' LIKE '%,null,%' THEN 'Y' ELSE 'N' END AS VALID
FROM test_data td;

By the way, it is generally bad practice to store unnormalized CSV data in your SQL tables. It would be better to instead store each component of every CSV string in a separate row, and logically connect them all via some other type of ID column. Then, you could use a regular WHERE clause to check for the presence of a null string value.