Oracle LEFT string compare throws a 'ORA-00904: "LEFT": invalid identifier' error message.

Jim Horn
Jim Horn used Ask the Experts™
Hi Guys

In the below code I'm trying to get all tables.columns in my schema that have a column name of B01 or B02.   That works fine, BUT I'm trying to filter out any tables that begin with 'INT', and when I include the commented-out line it throws an 'ORA-00904: "LEFT": invalid identifier' error message.  

Question:  What's the correct syntax for the commented-out line below?

SELECT  column_name, table_name
FROM    all_tab_columns
  AND olumn_name  IN ('B01', 'B02')
  -- AND LEFT(table_name, 3) != 'INT'     -- <--  This line is not working.. 
ORDER BY column_name, table_name;

Open in new window

Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
LEFT is not a valid Oracle function.  You need to use SUBSTR.

That would translate to:

SUBSTR(table_name, 1, 3)
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015


Bingo bango.  That works.  Thank you.
Most Valuable Expert 2012
Distinguished Expert 2018

Even though you already selected a solution I wanted to also throw out:
AND table_name not like 'INT%'
johnsoneSenior Oracle DBA

I was trying to give the Oracle version of the LEFT function.

What I would also like to point out is a very helpful link ->

That is the chapter of functions in the Oracle documentation.  For someone learning Oracle, it is a great reference.
Most Valuable Expert 2012
Distinguished Expert 2018

>>give the Oracle version of the LEFT function.

I figured but wanted to throw out the alternative for future reference.

In can be beneficial to not use functions on columns with an index.  A right handed wildcard still might use the index.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial