CODEWITHSUNDEEP
sqloracle-databaseplsql
GettingStarted
GettingStarted

Reputation: 7615

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

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

Upvotes: 0

Views: 133

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

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

Upvotes: 2

pmdba
pmdba

Reputation: 7033

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.

Upvotes: 0

Related Questions