UCASE and UPPER sql functions

Question

I am trying to do the following query:

String query = "SELECT * FROM EMP WHERE UCASE(LAST_NAME) ";
query += "LIKE '" + lastName.toUpperCase() + "%'";

in an example of usage of an servlet to access to a database But I am getting the error message:

Excepcion java.sql.SQLSyntaxErrorException: ORA-00904: "UCASE": invalid identifier

On the other hand, when I use the UPPER sql function, the example works but the results do not show the values of the LASTNAME column in uppercase. I do not understand what happens.

The database will be dropped, though only if the user using the application has the privilege to do that. @lad2025 is implying that you should sanitize your input before using that in a sql firing. — SubhasisM, Sep 20 '16 at 08:16

score 3 · Accepted Answer · answered Jun 11 '16 at 15:29

3

You're just comparing the upper case values, but you're selecting the actual values with select *

answered Jun 11 '16 at 15:29

James Z

12,209
10
24
44

Ok. But what happens with the UCASE function? – julianfperez Jun 11 '16 at 15:31
4

As far as I know UCASE is not a valid function on Oracle (or SQL Server, which you have also tagged) – James Z Jun 11 '16 at 15:37
I am using Oracle Database, 11g Release 2 (11.2) and I have found the following sql compatibility table: http://docs.oracle.com/cd/E11882_01/gateways.112/e12014/dev_apps.htm#CHDIEFEB But I am a newbie with databases and I do not understand the compability of UPPER and UCASE in this table. – julianfperez Jun 11 '16 at 16:27
1

Did you read what it says above the table? `Table 4-1 describes how Oracle database and the gateway handle SQL functions for a DB2 UDB for z/OS`. If you're not using anything like that, just use UPPER, it's the correct function. – James Z Jun 11 '16 at 17:13

score 1 · Answer 2 · answered Jun 11 '16 at 20:27

to get the uppercase name in your resultset you need to use UPPER in your select list, not UCASE, like this:

String query = "SELECT UPPER(LAST_NAME) AS UPPERNAME, * FROM EMP WHERE UPPER(LAST_NAME) ";
query += "LIKE '" + lastName.toUpperCase() + "%'";

What your code is doing here is building a query string named query. Once query is complete, it will be sent to the database for parsing and running.

When you are building a query to the database, you have to use the built-in database functions for the part of the query that the database is going to parse and run. So, in your example, Java is doing toUpperCase on lastName and then putting that literal into the query string that will go to the database. UPPER(LAST_NAME) is going into the query string as is, it will get passed to the database just like that and run by the database. So it needs to be a function that the database can parse and run: an Oracle function, not a Java function.

score 1 · Answer 3 · answered Nov 02 '18 at 07:53

UCASE is a DB2 function & not Oracle. For Oracle, you need to use UPPER .

Second part of your question is already answered by James Z.

Having said that, I am answering because previous answers didn't pointed out SQL injection problem with the way you listed your query.

Make it a habit to always execute parametrized queries with jdbc & not by directly appending values to query string.

String query = "SELECT * FROM EMP WHERE UCASE(LAST_NAME) LIKE ? ";

Your parameter would be - lastName.toUpperCase()+"%"

SQL Injection

UCASE and UPPER sql functions

3 Answers3