Display row where string column starts with specific letter

Question

I researched how to solve my problem, but the compiler I'm using keeps throwing an invalid identifier error. Let me explain.

I have a table with the columns and data types matching respectively

Id | City | State
------------------------
NUMBER | VARCHAR2(21) | VARCHAR(2)

I want to grab all non-duplicate cities which names start with the letter "a". I have tried the below query

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE "A%";

I have also tried

SELECT CITY
FROM STATION
WHERE CITY LIKE "A%"
ORDER BY CITY;

But I keep receiving

ORA-00904: "A%": invalid identifier

I have looked up questions with a similar problem, and the answer seems to integrate the "LIKE" condition. I have done that, but alas, an error. This seems like it should be an easy problem to solve. Any ideas?

Answer 1

Use single quotes! Double quotes are meant for identifiers only (such as column names, table names, and so on), hence the error you are getting.

On the other hand, single quotes stand for literal strings, which is what you mean here:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE 'A%';

In that regard, Oracle strictly follows the ANSI SQL specification. There are other databases that allow double quotes for strings, and use other characters to quote identifiers (MySQL for example)... but I personally find that it makes things more confusing that they would need to.