Solved

Query Company Names with Apostrophes

Posted on 2010-08-24
18
773 Views
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?
0
Comment
Question by:DRRobinson
  • 5
  • 4
  • 3
  • +3
18 Comments
 
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%'
0
 
LVL 84
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, "'", "''") & "%'"
0
 
LVL 58

Expert Comment

by:cyberkiwi
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.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 3

Expert Comment

by:ChomperD
ID: 33510711
These would do what you are asking:

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

SELECT *
  from employers
 where company LIKE 'Dunkin% Donuts%'
0
 
LVL 58

Expert Comment

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

Expert Comment

by:alainbryden
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

--
Alain
0
 

Author Comment

by:DRRobinson
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?
0
 
LVL 20

Expert Comment

by:alainbryden
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.
0
 
LVL 58

Expert Comment

by:cyberkiwi
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.
0
 
LVL 84
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.
0
 

Author Comment

by:DRRobinson
ID: 33517500
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.  
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33517624
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%", "'", "")
0
 

Author Comment

by:DRRobinson
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
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?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 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 & "*"
union
SELECT * from [employers] E2 where E1.company LIKE Replace('Me!txtCompanyName & "*", "'", "")
0
 

Author Comment

by:DRRobinson
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?
0
 
LVL 84
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
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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