Solved

Query Company Names with Apostrophes

Posted on 2010-08-24
18
767 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now