SQL Invalid identifier - only occurs when query is ran in script

Question

I am coding in SQL Developer for Oracle database and I have a simple query as follows:

select distinct location from Conference where name like '%SIGMOD%' and year >= 2003 and year <= 2008;

When I run the query by itself, I get the expected correct result. But when I run it as part of a script I get this error:

Error starting at line : 1 in command -
select distinct location from Conference where name like ‘%SIGMOD%’ and year >= 2003 and year <= 2008
Error at Command Line : 1 Column : 58
Error report -
SQL Error: ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.

I'm not sure what is going on.

EDIT: Even if I change the part of the query to where name = 'SIGMOD', I still get the same error.

EDIT 2: Silly me, I ran that query incorrectly. where name = 'SIGMOD' does get rid of the error. However, I wonder why I can't use the like statement in the original question?

Something looks odd with that apostophe: like ‘%SIGMOD%’ -- should be a single quote. ' is different than ‘. — sgeddes
– sgeddes, Commented Feb 19, 2016 at 1:40
Get rid of that portion of the where clause and see if it runs. I don't think it's the like vs =, but rather those apostrophes look off to me. If it runs without that part, you'll know that's the issue. — sgeddes
– sgeddes, Commented Feb 19, 2016 at 1:45
Yeah you're right. It was the apostrophes. Somehow they changed when copying and pasting from one editor to the other. Thanks. If you write that as answer I'll accept it as best. — Kingamere
– Kingamere, Commented Feb 19, 2016 at 1:51

sgeddes · Accepted Answer · 2016-02-19 01:55:24Z

1

You need to update the apostrophes to be single quotes. Change this:

select distinct location 
from Conference 
where name like ‘%SIGMOD%’ and year >= 2003 and year <= 2008

to:

select distinct location 
from Conference 
where name like '%SIGMOD%' and year >= 2003 and year <= 2008

answered Feb 19, 2016 at 1:55

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Invalid identifier - only occurs when query is ran in script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related