Help dealing with apostrophe in a search

I need some help with a database search. I have a database
of bar
listings. The bar names have an apostrophe in them (ie.
Jack's Bar).
When I enter the search criteria "Jack's"
everything works fine,
but if I enter Jacks, I get no results. I don't really want
to remove
the apostrophe from the bar names because it looks better
with them
when it is displayed on the web site.

How can I ignore the apostrophe coming out of the database
so Jack's
and Jacks will return the same results?


--~--~---------~--~----~------------~-------~--~----~
This group is managed and maintained by the development
staff at 360 PSG. An enterprise application development
company utilizing open-source technologies for todays
small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the
Google Groups "Professional PHP Developers"
group.
To post to this group, send email to Professional-PHPgooglegroups.com
To unsubscribe from this group, send email to
Professional-PHP-unsubscribegooglegroups.com
For more options, visit this group at http:
//groups.google.com/group/Professional-PHP
-~----------~----~----~----~------~----~------~--~---

Why not do select * from table WHERE name LIKE 'jacks'
that will match
jack's jack kacks ect.

Flamer.


--~--~---------~--~----~------------~-------~--~----~
This group is managed and maintained by the development
staff at 360 PSG. An enterprise application development
company utilizing open-source technologies for todays
small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the
Google Groups "Professional PHP Developers"
group.
To post to this group, send email to Professional-PHPgooglegroups.com
To unsubscribe from this group, send email to
Professional-PHP-unsubscribegooglegroups.com
For more options, visit this group at http:
//groups.google.com/group/Professional-PHP
-~----------~----~----~----~------~----~------~--~---

Irfan Sayed wrote:
> Hi,
>
> I think this should do
>
> SELECT * FROM table_name WHERE replace(Name ,' \'',
'' ) LIKE 'jack%'
> union
> SELECT * FROM table_name WHERE Name  LIKE 'jack%'
>
> this will match
> -- jack bar
> --jack's bar
>
> hope this helps
>
> happy coding.
>
> Irfan
>
Building on this, it may be easier to just strip out
punctuation from
the field:

SELECT * FROM table where replace(Name, '\'', '') LIKE
'jack%'

Mysql also has a fulltext search ability, which might be of
use to you
(milage may vary with it)


--~--~---------~--~----~------------~-------~--~----~
This group is managed and maintained by the development
staff at 360 PSG. An enterprise application development
company utilizing open-source technologies for todays
small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the
Google Groups "Professional PHP Developers"
group.
To post to this group, send email to Professional-PHPgooglegroups.com
To unsubscribe from this group, send email to
Professional-PHP-unsubscribegooglegroups.com
For more options, visit this group at http:
//groups.google.com/group/Professional-PHP
-~----------~----~----~----~------~----~------~--~---

Thanks for all of your help, I wish it was as easy as
stripping out the
'. The search works fine with Jack's, it is Jacks that
messes up. I'd
either need to add a ' on every search that ends with an
's' (would not
work). Or somehow ignore the ' in the query results.
however, when i
display the results, I'd still want the ' in it.

right now my sql is SELECT * FROM barlist WHERE barname LIKE
'var.barname%'


--~--~---------~--~----~------------~-------~--~----~
This group is managed and maintained by the development
staff at 360 PSG. An enterprise application development
company utilizing open-source technologies for todays
small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the
Google Groups "Professional PHP Developers"
group.
To post to this group, send email to Professional-PHPgooglegroups.com
To unsubscribe from this group, send email to
Professional-PHP-unsubscribegooglegroups.com
For more options, visit this group at http:
//groups.google.com/group/Professional-PHP
-~----------~----~----~----~------~----~------~--~---

pinniger wrote:
> Thanks for all of your help, I wish it was as easy as
stripping out the
> '. The search works fine with Jack's, it is Jacks
that messes up. I'd
> either need to add a ' on every search that ends with
an 's' (would not
> work). Or somehow ignore the ' in the query results.
however, when i
> display the results, I'd still want the ' in it.
>
> right now my sql is SELECT * FROM barlist WHERE barname
LIKE
> 'var.barname%'

stripping it of ' in the where clause won't affect the
result in the
select clause.


--~--~---------~--~----~------------~-------~--~----~
This group is managed and maintained by the development
staff at 360 PSG. An enterprise application development
company utilizing open-source technologies for todays
small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the
Google Groups "Professional PHP Developers"
group.
To post to this group, send email to Professional-PHPgooglegroups.com
To unsubscribe from this group, send email to
Professional-PHP-unsubscribegooglegroups.com
For more options, visit this group at http:
//groups.google.com/group/Professional-PHP
-~----------~----~----~----~------~----~------~--~---