Re: Help needed in Search

On Wed, 2011-09-28 at 12:33 +0530, Siva Palanisamy wrote:

> Hi All,
>
>
>
> I am trying to retrieve the contact names based on the keyed search
> string. It performs good for the English alphabets and behaves
> strangely for special chars such as _,/,\,%
>
>

The % character is used by SQL as the wild card for searching. To search
for Mecklenburg county (in North Carolina) from a list of US counties
you might try meck% to find all the counties that start with meck. (%
meck for those that end in meck and %meck% for any that contain meck).
The use of % is in the SQL standard and is used by all the all the SQL
dialects I am familiar with.

>
> My query in the function is similar to
>
>
>
> SELECT contact_name FROM contacts WHERE LOWER(contact_name) LIKE
> LOWER('_McDonald%') ORDER BY LOWER(contact_name) ASC LIMIT 1;
>

I would expect you to get anything that starts with _mcdonald
(_mcdonald, james) not say james mcdonald (%mcdonald% would work) . The
underline (_) is not the same as a space. To search with leading space
try '% mcdonald%'

>
> It looks like, during searching, it retrieves all the contact names
> instead of the desired. The similar bizarre happens for the above
> mentioned special chars. I need to support these. How do I educate
> postgres to consider these chars? Please guide me.
>
>
>
> Thanks and Regards,
>
> Siva.
>
>
>
>
> ______________________________________________________________________
>
> ::DISCLAIMER::
> -----------------------------------------------------------------------------------------------------------------------
>
> The contents of this e-mail and any attachment(s) are confidential and
> intended for the named recipient(s) only.
> It shall not attach any liability on the originator or HCL or its
> affiliates. Any views or opinions presented in
> this email are solely those of the author and may not necessarily
> reflect the opinions of HCL or its affiliates.
> Any form of reproduction, dissemination, copying, disclosure,
> modification, distribution and / or publication of
> this message without the prior written consent of the author of this
> e-mail is strictly prohibited. If you have
> received this email in error please delete it and notify the sender
> immediately. Before opening any mail and
> attachments please check them for viruses and defect.
>
> -----------------------------------------------------------------------------------------------------------------------

--
Jay Lozier
jslozier(at)gmail(dot)com