Query Company Names with Apostrophes

replace one single quote with 2 single quotes:
SELECT * from [employers] where company LIKE 'o''leary%'

Or you can eliminate the single quote while searching
SELECT * from [employers] where replace(company,'''','') LIKE 'oleary%'

You need to "escape" those apostrophes:

SELECT * FROM [employers] WHERE company LIKE "o''leary%"

Note those are 2 single apostrophes after the "o", not a double quote. You can use the replace function if you need to do this dynamically:

SELECT * FROM [employers] WHERE company LIKE '" &  Replace(YourValue, "'", "''") & "%'"

Having actually read your question (!), try these

SELECT * from [employers] where Replace(company, "'", "") LIKE ''oleary%"

Unfortunately, this won't perform well because there is no index on the made up column by replacing company.
For such a problem, I typically store and index a computed column (sql server 2005+) that is the result of replacing ' -> nothing.

SELECT * from [employers] where companyNoQuote LIKE Replace(''o'leary%", "'", "''")

In the 2nd query, I show how you can take input as-is and do the stripping of ' in SQL.

These would do what you are asking:

SELECT *
  from employers
 where company LIKE 'o%leary%'

SELECT *
  from employers
 where company LIKE 'Dunkin% Donuts%'

Given your LIKE syntax (%) I take it this is back-ended in SQL Server?
Access uses * for wildcards.

Use this,

SELECT * from [employers] where Replace( company, "'", "" ) LIKE Replace( paramCompany, "'", "" )

It will simply search for company names with the apostrophes stripped out, so it will return o'leary, oleary, o'l'e'a'r'y, etc. Careful, it won't return o leary - it's stripping out apostrophes, but not replacing them with spaces.

This will solve your specific problem but it sounds like what you should be looking at in the long run is a fuzzy search - so take a look at SQL's Full-Text Search: http://msdn.microsoft.com/en-us/library/ms142571.aspx

--
Alain

I'm using Access as the BE.  As near as I can tell none of the suggestions will work.  Here's the situation:

when someone enter as company name in the search form (web), the form is submitted and if they've entered a value in the company field, let's say 'oleary'.  There are no apostrophes in this input so a replace like Replace(Current.Request.Form("company"), "'", "''") still will not return the record O'leary.

the empolyer table contains records for both an Oleary and O'leary.  If I strip the single quote, then O'leary will not be returned.  if I escapt the single quote with two single qoutes (o''leary) then the Oleary record will not be returned, or am I missing somethign?

You are missing something - we're saying that in the database column you're searching, you should be comparing your search value to the value in that column with apostrophe's replaced.

See http:#a33510704

The gist is

The WHERE clause strips ' from both sides.
The better alternative is to have a computed column that already has ' stripped
    Alter table X Add ColComputed as Replace(Col, "'","")
    Create index IX_X_ColComputed ON X(ColComputed)

Now, your select statement returns the ACTUAL, full name with ' in the original field

SELECT company, other1, other2, ...
from [employers]
where companyNoQuote LIKE Replace(''o'leary%", "'", "''")

"Company" returned in the result contains the single quotes unaffected.
The replacement (or use the indexed stripped column) is used in the WHERE filter against the stripped right-hand-side of LIKE.

Have you tried the suggestions?

I'd argue that "oleary" is NOT the same as "o'leary", and therefore a search using the escaped single quote should NOT return a record with "oleary" in it. Are you instead saying that a search for "o'leary" should return "oleary", "o'leary", "ol'eary" and any other derivations of that word? If so, you're in for a much more difficult task than a simple search.

LSMConsulting:

Yes, that is the goal, I guess there is no simple single query that will return all the desired results.  My thinking now is that I'll strip out any and all apostrophes from the submitted search creteria, then do another quiery without stripping the apostrophes and combine both the results as the output.  

DRRobinson,

Since the backend is Access, computed columns are out then.

records exist: o'brien, obrienne

(your current thinking)
input: O'brien
search on : o'brien, obrien

(computed column solution)
computed column stores: obrien, obrienne
input: O'brien
search on: obrien
retrieves both on search by just "obrien"

(Access solution)
SELECT * from [employers] E1 where E1.company LIKE ''o'leary%"
union
SELECT * from [employers] E2 where E1.company LIKE Replace(''o'leary%", "'", "")

Bear in mind that O'leary is just an example of one of the company names.  This customer wants to be able to search for 'oleary' and have all of the following resl;ts returned.

Oleary Paints,
O’leary paints.
Ole’ary Paints
Olea’ry Paints
Olear’y Pants
Oleary’ Paints
Oleary Crain Operating  Services’
Oleary’ Tavern
Steal Contruction by Oleary
Etc…

I can see his point at how useful this could be, there were employees doing searches, for example, 'oleary', and if not found, they would enter that company again creating duplicates.   I think I know how I'm goint to deal with this.  

First strip the first charate off the searh term '0', then do a select where all companies begin with 'O'.  Then while looping through the result set, strip all apostrophes from the company_name result set and compare to the form subbmited company name (with apostrophes stripped and a wild card added to the end).  If it's a match then that company will be included in the output.  

Any thoughts?

Hi, Sorry to drag this on, but I'm not using VBA.  I'm using a VB.NET 2005 FE, with an access backend.  Any advice on syntax using vb.NET?

The suggestion by cyberkiwi would work regardless of whether it's run in Excel, Access, VB.NET, etc etc. .NET would just use a DataAdapter of some sort, which can accept Access-specific syntax.

Accept http:#a33526738