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

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
LVL 67
Jim HornSQL Server Data DudeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
LEFT is not a valid Oracle function.  You need to use SUBSTR.

That would translate to:

SUBSTR(table_name, 1, 3)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornSQL Server Data DudeAuthor Commented:
Bingo bango.  That works.  Thank you.
slightwv (䄆 Netminder) Commented:
Even though you already selected a solution I wanted to also throw out:
AND table_name not like 'INT%'
johnsoneSenior Oracle DBACommented:
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.
slightwv (䄆 Netminder) 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.