Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

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


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?
1 Solution
Helena Markováprogrammer-analystCommented:
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.

panpanWAuthor Commented:
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?

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now