PostgreSQL LIKE operator doesn't match hyphen

Question

I've a problem with LIKE operator in PostgreSQL. It does not match patterns that contain the - character. I've tried to escape these characters using the ESCAPE option, but it still does not work.

Query:

select * from footable where trascrizione like '% [---]is Abraam %';

Sample data (contents of column trascrizione):

[---]is Abraam [e]t Ise[---] / ((crux quadrata))

How can I solve this problem?

this is a semplified example of my query: select * from footable where trascrizione like '% [---]is Abraam %'; it returns a void result set (but I know that there's one record that matchs). — Antonio F.
– Antonio F., Commented Aug 20, 2011 at 18:48
The row that has this value for the field trascrizione: "[---]is Abraam [e]t Ise[---] / ((crux quadrata))" — Antonio F.
– Antonio F., Commented Aug 20, 2011 at 18:53
@Antonio F.: Your duty is to clarify your question by editing it, not by making it clear in the comments. The question should be answerable by just reading the question. — user unknown
– user unknown, Commented Aug 20, 2011 at 19:04

cdhowie · Accepted Answer · 2011-08-20 18:55:39Z

1

That pattern would not match because there is no space before [---]is Ambraam. There is a space in your pattern, between the % and [ characters, and it's requiring that space to be in your data. Try LIKE '%[---]is Abraam %'.

answered Aug 20, 2011 at 18:55

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Antonio F. Over a year ago

Thank you. The problem was not the abscence of whitespace between '[' and '-', but a whitespace before the '%' and '['. I have to trim the string.

user unknown · Accepted Answer · 2011-08-20 18:56:07Z

0

Please show the code. I have no problem:

SELECT * FROM a WHERE a LIKE '%-' AND b LIKE '%-%' AND c LIKE '-%';
  a   | b |  c   | d | e 
------+---+------+---+---
 foo- | - | -bar |   | 
(1 Zeile)

answered Aug 20, 2011 at 18:56

user unknown

36.4k12 gold badges77 silver badges123 bronze badges

Collectives™ on Stack Overflow

PostgreSQL LIKE operator doesn't match hyphen

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related