Why is my concatenation not allowing me to append a % to the end?

Question

SELECT COUNT(*) INTO E_COUNT FROM EXAMPLE_TABLE WHERE UPPER(EMPLOYEE_USERNAME) LIKE CONCAT(UPPER("example"), "%");

I am getting the following error

ORA-00904: "%": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 1 Column: 111

'%' is a special character so you need to escape it in a string, this question tells you how to do that: [Oracle pl-sql escape character (for a " ' ")](https://stackoverflow.com/questions/11717159/oracle-pl-sql-escape-character-for-a) — JeffUK, May 05 '20 at 16:50
Double quotes are for identifiers (e.g. table and column names) if you need them. Single quotes for string literals. — Mat, May 05 '20 at 16:51
What the message says is that you don't have a column called `%` in any table or view. Literal strings must be single-quoted. — Álvaro González, May 05 '20 at 17:00
Does this answer your question? [What is the difference between single and double quotes in SQL?](https://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql) — Álvaro González, May 05 '20 at 17:01

score 2 · Accepted Answer · answered May 05 '20 at 16:56

2

When you use double quotes, it is interpreted as an identifier.

with t ("ab", "%") as (
select 1, 2 from dual
)
select concat("ab", "%") from t;

CO
--
12

answered May 05 '20 at 16:56

Slkrasnodar

score 0 · Answer 2 · answered May 05 '20 at 16:52

0

Should be like this:

SELECT COUNT(*) INTO E_COUNT FROM EXAMPLE_TABLE WHERE UPPER(EMPLOYEE_USERNAME) LIKE UPPER('example%');

or if you're comparison is to a bind variable, then like this:

SELECT COUNT(*) INTO E_COUNT FROM EXAMPLE_TABLE WHERE UPPER(EMPLOYEE_USERNAME) LIKE UPPER(:V_EXAMPLE)||'%';

Use single quotes (double quotes are for labels, not literal values), and just put the wildcard right in with the text.

answered May 05 '20 at 16:52

pmdba

I am eventually going to be replacing example with a parameter. So you're saying :ParameterName – software is fun May 05 '20 at 16:55
Exact syntax (with or without leading colon : ) depends on whether the parameter is initialized in the code or passed in as a procedure input, but either way use ||'%' to append the wildcard to the parameter value. – pmdba May 05 '20 at 17:22

2 Answers2