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??