Get table names that do not contain "_" in Oracle

Question

I am working a script that is supposed to grab a list of table names that contain a specified column, and exclude the table names that contain an underscore. I have tried:

SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE 
    COLUMN_NAME = 'SERIAL_NUMBER' AND
    TABLE_NAME NOT LIKE '%\_%';

This query still pulls table names that contain an underscore. What am I missing?

Answer 1

Underscore is a wildcard in LIKE statements - it matches exactly one character. Try this instead:

SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = 'SERIAL_NUMBER' 
AND instr(TABLE_NAME, '_') = 0;

---

After posting I noticed that you actually have attempted to escape the underscore. There is no default escape character for LIKE so you need to tell the database what you're doing:

  SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = 'SERIAL_NUMBER' 
AND TABLE_NAME  NOT LIKE '%\_%' ESCAPE '\';

Find out more.