Query Company Names with Apostrophes

I'm trying to construct a query that will return company names using a SELECT query.  My problme is that some companies have an apostrophe in the name.  Is there a way to have a single query return companies with or without an apostrophe in the name?  For eample:

SELECT * from [employers] where company LIKE ''o'leary%"
But want the query to return any matches that have an apostrophe or not.  Another example: there are two companies in the employer table, one named Dunkin Donuts, and other named Dunkin' Donuts.  These are in fact two separate employers with different locations, but I need the query to return both.

Any ideas?
Who is Participating?
cyberkiwiConnect With a Mentor Commented:
[First strip the first charate off the searh term '0', then do a select where all companies begin with 'O'.]

Why loop through 1000 records that start with O and inspect them in VBA, when your query can do it in one hit?

(Access solution)
SELECT * from [employers] E1 where E1.company LIKE Me!txtCompanyName & "*"
SELECT * from [employers] E2 where E1.company LIKE Replace('Me!txtCompanyName & "*", "'", "")
Om PrakashCommented:
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%'
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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, "'", "''") & "%'"
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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:

  from employers
 where company LIKE 'o%leary%'

  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

DRRobinsonAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
DRRobinsonAuthor Commented:

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.  

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%"
SELECT * from [employers] E2 where E1.company LIKE Replace(''o'leary%", "'", "")
DRRobinsonAuthor Commented:
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

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?
DRRobinsonAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.