how to write the query with "'" in the middle of the value for Oracle ?

Posted on 2004-11-22
Last Modified: 2010-03-31

i am try to write this query, but keep getting Oracle ora-1008, NOT ALL VARIABLES BOUND error when I am trying to select a row with name like " D'Andrea Mary".

 String query = "select id from users where last||', '||first = ?";
      PreparedStatement pst = conn.prepareStatement(query.toString());
      pst = conn.prepareStatement(query);
      pst.setString(1, name);
      ResultSet rs = pst.executeQuery(query);

how to re-write this query to make it work?
Question by:panpanW
    LVL 22

    Expert Comment

    If you want to use such a string in Oracle:
    'select id from users where name=D'Andrea Mary'
    it ought to be like this:
    'select id from users where name=D'''Andrea Mary' -> it is 3 times '

    I hope this will be a little help for you.


    Author Comment

    thanks. actually, it should be '' , 2 times for this query to work.

    but, my case is I need to write something "generic", that fits all kind of values, which is a variable. any advice?

    LVL 35

    Accepted Solution

    I suggest you do a search and replace on the ' character. Look at the replaceAll method of the String class and replace the single quote with two single quotes so you will tell oracle to escape the next single quote.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
    This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now