Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select with string that has a lone hyphen yields nothing

Select with string that has a lone hyphen yields nothing

From
Beevee
Date:
I have a product names table like this:

datab=# select product_id, name from table.product_synonyms where name
ilike '%%olympus e-pl1%%';
 product_id
|
name
------------

+---------------------------------------------------------------------------------------------------------------------------------------
       8736 | Olympus E-PL1
       8736 | Olympus E-PL1 Interchangeable Lens Type Live View
Digital Camera – Black (Body Only) (Call for pre-order. Available on:
2010-04-09)
       8736 | Olympus E-PL1 Interchangeable Lens Type Live View
Digital Camera – Blue (Body Only)
       8736 | Olympus E-PL1 Interchangeable Lens Type Live View
Digital Camera w/ ED 14-42mm f3.5-5.6 (champagne/gold)
(4 rows)

Any select statement prior to the hyphen yields a result, but from the
hyphen on yields nothing:

datab=# select product_id, name from table.product_synonyms where name
ilike '%%Olympus E-PL1 Interchangeable Lens Type Live View Digital
Camera -  Blue %%';
 product_id | name
------------+------
(0 rows)

Any ideas how to fix this?

Re: Select with string that has a lone hyphen yields nothing

From
Kenichiro Tanaka
Date:
Hi

The hyphen which written in 'Olympus E-PL1' is different from
the one which written in 'Camera - Black'.

em-dash
http://www.fileformat.info/info/unicode/char/2014/index.htm
en-dash
http://www.fileformat.info/info/unicode/char/2013/index.htm
figure-dash
http://www.fileformat.info/info/unicode/char/2012/index.htm

I have no idea to fix using PostgreSQL's function,because they don't equal.
I think you have to change the data or change the behavior of your
application .

Thank you.

> I have a product names table like this:
>
> datab=# select product_id, name from table.product_synonyms where name
> ilike '%%olympus e-pl1%%';
>   product_id
> |
> name
> ------------
>
+---------------------------------------------------------------------------------------------------------------------------------------
>         8736 | Olympus E-PL1
>         8736 | Olympus E-PL1 Interchangeable Lens Type Live View
> Digital Camera – Black (Body Only) (Call for pre-order. Available on:
> 2010-04-09)
>         8736 | Olympus E-PL1 Interchangeable Lens Type Live View
> Digital Camera – Blue (Body Only)
>         8736 | Olympus E-PL1 Interchangeable Lens Type Live View
> Digital Camera w/ ED 14-42mm f3.5-5.6 (champagne/gold)
> (4 rows)
>
> Any select statement prior to the hyphen yields a result, but from the
> hyphen on yields nothing:
>
> datab=# select product_id, name from table.product_synonyms where name
> ilike '%%Olympus E-PL1 Interchangeable Lens Type Live View Digital
> Camera -  Blue %%';
>   product_id | name
> ------------+------
> (0 rows)
>
> Any ideas how to fix this?
>
>


--
================================================
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
================================================

Re: Select with string that has a lone hyphen yields nothing

From
Craig Ringer
Date:
On 30/04/2010 5:33 PM, Kenichiro Tanaka wrote:
> Hi
>
> The hyphen which written in 'Olympus E-PL1' is different from
> the one which written in 'Camera - Black'.
>
> em-dash
> http://www.fileformat.info/info/unicode/char/2014/index.htm
> en-dash
> http://www.fileformat.info/info/unicode/char/2013/index.htm
> figure-dash
> http://www.fileformat.info/info/unicode/char/2012/index.htm
>
> I have no idea to fix using PostgreSQL's function,because they don't equal.
> I think you have to change the data or change the behavior of your
> application .

The usual solution to this sort of thing is to provide a functional
index on the problem field that computes a "simplified" version of the
text - stripping accents, dumbing all dashes down to simple minus signs,
etc.

I'm not aware of any canned tool to do this in PostgreSQL. Everyone's
needs seem to vary, so it'd be hard to provide one.

--
Craig Ringer