Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Query Company Names with Apostrophes

Posted on 2010-08-24
Medium Priority
Last Modified: 2012-06-27
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?
Question by:DRRobinson
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
LVL 22

Expert Comment

by:Om Prakash
ID: 33510662
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%'
LVL 85
ID: 33510676
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, "'", "''") & "%'"
LVL 58

Expert Comment

ID: 33510704
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 33510711
These would do what you are asking:

  from employers
 where company LIKE 'o%leary%'

  from employers
 where company LIKE 'Dunkin% Donuts%'
LVL 58

Expert Comment

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

Expert Comment

ID: 33510734
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


Author Comment

ID: 33512110
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?
LVL 21

Expert Comment

ID: 33512676
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.
LVL 58

Expert Comment

ID: 33515938
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.
LVL 85
ID: 33516289
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.

Author Comment

ID: 33517500

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.  
LVL 58

Expert Comment

ID: 33517624

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%", "'", "")

Author Comment

ID: 33526613
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?
LVL 58

Accepted Solution

cyberkiwi earned 2000 total points
ID: 33526738
[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 & "*", "'", "")

Author Comment

ID: 33533475
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?
LVL 85
ID: 34297060
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question