Re: select * from test where name like 'co_%'

On Tue, Mar 10, 2020 at 12:49:01PM +0000, sivapostgres@yahoo.com wrote:

Hello,
What returns when I run a query like this;
Select * from test where name like 'co_%';
I expect anything that starts with 'co_' and NOT 'co' only.� Am I right?� But I get every names that starts with 'co'. Why ?
Happiness Always
BKR Sivaprakash

Hi,

Check out the documentation:

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE

You have 2 meta characters there. The '_' matches any character also.

Regards,
Ken

Hi,

an underscore matches a single character, any character. You'd have to
escape it and tell the query what the escape character is if you want
it to be treated as a standard character:

db=# create table t(t text);
CREATE TABLE
db=# insert into t(t) values ('fox'), ('fo_'), ('fo_x');
INSERT 0 3
db=# select * from t;
t
------
fox
fo_
fo_x
(3 rows)

db=# select * from t where t like 'fo_%';
t
------
fox
fo_
fo_x
(3 rows)

db=# select * from t where t like 'fo\_%' escape '\';
t
------
fo_
fo_x
(2 rows)

Cheers,
Paul

On Tue, Mar 10, 2020 at 1:49 PM sivapostgres@yahoo.com
<sivapostgres@yahoo.com> wrote:

Thanks.  Got it.
On Tuesday, 10 March, 2020, 06:30:27 pm IST, Paul Foerster <paul.foerster@gmail.com> wrote:

Hi,

an underscore matches a single character, any character. You'd have to
escape it and tell the query what the escape character is if you want
it to be treated as a standard character:

db=# create table t(t text);
CREATE TABLE
db=# insert into t(t) values ('fox'), ('fo_'), ('fo_x');
INSERT 0 3
db=# select * from t;
  t
------
fox
fo_
fo_x
(3 rows)

db=# select * from t where t like 'fo_%';
  t
------
fox
fo_
fo_x
(3 rows)

db=# select * from t where t like 'fo\_%' escape '\';
  t
------
fo_
fo_x
(2 rows)

Cheers,
Paul

On Tue, Mar 10, 2020 at 1:49 PM sivapostgres@yahoo.com
<sivapostgres@yahoo.com> wrote: