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

Jim Horn
Jim Horn used Ask the Experts™
on
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
WHERE  owner = 'JHORN_SANDBOX'
  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.
Jim
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
Commented:
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

Author

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

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

Commented:
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 -> http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions.htm#SQLRF006

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

Commented:
>>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