• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difficulty in SQL commands to find records with apostrophe exclusively

 
Marvin Legend
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a database that has stored records containing apostrophe, for example: McDonald 's, Bill 's, Anne's etc. ..

I haven't had a lot of trouble to insert these records with apostrophe, but I'm having trouble finds them solely, generally when I want to find a unique record do this way:

SELECT CODE FROM ' TBL_TEST ' WHERE NOME_TESTE = ' Ana '
He returns an exact result = 35


But if the word sought have apostrophe the previous example doesn't solve my problem, so I changed my code to this:

SELECT CODE FROM ' TBL_TEST ' WHERE NOME_TEST LIKE '% Paulo ' s% '

Observe that now I use the LIKE clause to search for words with apostrophes or without (my database has many words with apostrofo) because the = does not find them but this search with the LIKE clause returns to me not only the name of Paulo's but all containing Paul's and the result is this:

result = Paulo ' s
Paul's Sheen
Paul ' s Gates
Paul's Jobs


So I would like to find unique records and with apostrophe, because the examples you note that the two things are not happening together, I would like in the second example to find only Paul's and wouldn't want to find Paul's Sheen, Paul's Gates, Paul's Jobs.

Stay in peace guys.
 
Rob Spoor
Sheriff
Pie
Posts: 20750
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use a PreparedStatement and you can use = without a problem:
 
Chris Barrett
Bartender
Posts: 317
24
Eclipse IDE Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Marvin,

Rob's answer is best, but if this is just from a SQL statement perspective you can escape an apostrophe by using two apostrophes together:

SELECT CODE FROM ' TBL_TEST ' WHERE NOME_TEST LIKE '%Paulo''s%'

Oh, and if you want the ResultSet to only return Paul's and not Paul's Sheen, remove the second percentage sign from your LIKE statement:

SELECT CODE FROM ' TBL_TEST ' WHERE NOME_TEST LIKE '%Paul''s'

The % is SQL for a wildcard representing 0 or more characters.

Cheers!
Chris
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic