CODEWITHSUNDEEP
sqloracle-database
J Meister
J Meister

Reputation: 13

Cannot run simply query, getting "missing right parenthesis" error

What is wrong with this query?

select author_num from (henry_author where (contains(author_first,'Albert') > 0))

Keeps giving me an error that is is missing a right parenthesis?

Upvotes: 1

Views: 153

Answers (1)

SnakeDoc
SnakeDoc

Reputation: 14371

SELECT author_num FROM henry_author WHERE author_first LIKE '%Albert%';

or, probably better to account for data inconsistencies:

SELECT author_num FROM henry_author WHERE UPPER(author_first) LIKE '%ALBERT%';

The % is a wildcard matching zero or more characters. So %ALBERT% means anything can be before or after 'ALBERT', which is effectively what your contains() function is doing.

UPPER is just a function which converts the string into upper case characters, which makes it easier to deal with potential data inconsistencies, ie. someone typed in 'albert' instead of 'Albert', etc.

Since you're using JDBC, you might want to structure your query to use PreparedStatement which will allow you to parameterize your query like so:

final String sqlSelectAuthorNum = "SELECT author_num FROM henry_author WHERE UPPER(author_first) LIKE ?";
final PreparedStatement psSelectAuthorNum = conn.prepareStatement(sqlSelectAuthorNum);

// now execute your query someplace in your code.
psSelectAuthorNum.setString(1, "%" + authorName + "%");
final ResultSet rsAuthorNum = psSelectAuthorNum.executeQuery();
if (rsAuthorNum.isBeforeFirst()) {
    while (rsAuthorNum.next()) {
        int authorNumber = rsAuthorNum.getInt(1);
        // etc...
    }
}

Upvotes: 1

Related Questions