CODEWITHSUNDEEP
javaoracle-database
Mihir
Mihir

Reputation: 581

How to handle a single quote in Oracle SQL?

I have a query constructed in Java to search and fetch records,when constructing with the values ' (Single Quote)(For Example:New's) then I'm not getting any results.

Here is the query where I am setting the value New's:

SELECT COUNT(1) FROM AGX_THERAPEUTIC_AREA T WHERE  UPPER(T.THERAPEUTIC_NAME) LIKE ? ESCAPE \  OR  UPPER(T.THERAPEUTIC_AREA_DESC) LIKE ? ESCAPE \ 
    bind => [NEW'S%, NEW'S%]

Here is the method in which i am handling single quote:

public static String handleSingleQuote(String searchString) {
        if ((searchString == null) || searchString.trim().equals("")) {
            searchString = "";
        }

        searchString = searchString.trim();

        int length = searchString.length();
        StringBuffer searchBuffer = new StringBuffer();

        for (int index = 0; index < length; index++) {
            if (searchString.charAt(index) == '\'') {
                searchBuffer.append("''");
            } else {
                searchBuffer.append(searchString.charAt(index));
            }
        } // end of for loop

        searchString = searchBuffer.toString();

        return (searchString);
    }

Upvotes: 0

Views: 1413

Answers (4)

T.J. Crowder
T.J. Crowder

Reputation: 1074168

I have a query constructed in Java to search and fetch records,when constructing with the values ' (Single Quote)(For Example:New's) then I'm not getting any results.

Then you're constructing it incorrectly. It sounds like you're using string concatenation. Never do that (see below). Instead, use PreparedStatement:

PreparedStatement ps = connection.prepareStatement(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE ? ESCAPE \\"
);
ps.setString("this 'has' single quotes");
ResultSet rs = ps.executeQuery();

Your JDBC connector will ensure that the string is sent through correctly.

Re your comment saying you're using JPA: I don't know JPA, but this page suggests it would look something like:

TypedQuery<Thingy> query = em.createQuery(
    "SELECT FOO FROM BAR WHERE COLUMN LIKE :search ESCAPE \\",
    Thingy.class
);
return query.setParameter("search", "this 'has' quotes").getSingleResult();

Re "never do that":

enter image description here

Upvotes: 4

Mihir
Mihir

Reputation: 581

public static String handleSingleQuote(String searchString) {
        if ((searchString == null) || searchString.trim().equals("")) {
            searchString = "";
        }

        searchString = searchString.trim();

        int length = searchString.length();
        StringBuffer searchBuffer = new StringBuffer();

        for (int index = 0; index < length; index++) {
            if (searchString.charAt(index) == '\'') {
                searchBuffer.append("\'");
            } else {
                searchBuffer.append(searchString.charAt(index));
            }
        } // end of for loop

        searchString = searchBuffer.toString();

        return (searchString);
    }

Upvotes: 0

Anupam Saini
Anupam Saini

Reputation: 2421

Escape it using quotes

SELECT 'New''s' AS text FROM DUAL;

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

You can escape it by making it two single quotes.

select 'Hello y''all!' from dual

Upvotes: 2

Related Questions