SQLServer's REPLACE command seems to work like Oracle's TRANSLATE function, but you can get rid of apostrophe and spaces:

WHERE REPLACE(REPLACE(LastName, '''', ''), ' ', '') LIKE 'Ob%'

Would find all the O'Brian forms (except O'Bien).

---

>I work in Oracle, so see if there is an equivalent way on SQL Server.
>
>Oracle, like VFP, has a soundex function. So something like --
>
>where soundex(lastname) = soundex('OBrian')
>
>-- will return all your examples. Unfortunately, you do have to have the entire lastname. My example also returns all your examples when the right side is 'OBrien'
>
>Otherwise, you are stuck with using LIKE or INLIST in the WHERE clause. There is also a TRANSLATE function, but it will not translate an apostrophe to nothing. You have to replace the apostrophe with another character.
>
>AFAIK, there is just no convenient way of dealing with non-VFP data that has apostrophes.
>
>>What's the best SELECT technique to use to grab all of the O'Brian's from the last name field. They could be stored as
>>
>>O'Brian
>>OBrian
>>O Brian
>>
>>We need to be able to allow the user to enter "Ob" in a text box and get people who's last name starts with "Ob", including all the variations on O'Brian. The same will apply with McDonald, Mc Donald...you get the picture.
>>
>>Any ideas??