Searching for

• Steve Riley-170199

  SSC Rookie

  Points: 43

  More actions

  September 17, 2004 at 4:00 pm

  #164922

  Hi All

  How do you search for a name with a ' in it,

  eg O'Connor

  i tried in Query to search a table for the name, i typed in

  =O'Connor

  and it shows

  SELECT surname

  FROM Students

  WHERE (surname = 'OConnor')

  is there a way to search for names with a ' in them ?

  Thanks

  Steve

• Carl Farmer

  SSCrazy

  Points: 2197

  More actions

  September 18, 2004 at 10:44 am

  #523582

  Try the following:

  SELECT Surname

  FROM Students

  WHERE Surname LIKE 'O%Connor'

  or something like this:

  --Get ASCII value for character

  SELECT ASCII(')

  SELECT Surname

  FROM Students

  WHERE REPLACE(Surname, CHAR(Insert ASCII Value), '') = 'OConnor'

  ** I havent tested the last example - I am not sat in front of a PC with SQLQA on it, so am guessing a little, but it may help you? **

• Steve Riley-170199

  SSC Rookie

  Points: 43

  More actions

  September 18, 2004 at 11:22 am

  #523589

  Thank you very much,

• Douglas De Ivey

  Old Hand

  Points: 396

  More actions

  September 18, 2004 at 12:16 pm

  #523593

  Hi,

   

  You just have to double up the ' as in

  select * from test where data = 'O''Connor'

  If you are creating the statement dynamically from say a web page you can use the replace function swap each single quote with two singe quotes