CODEWITHSUNDEEP
postgresqlsql-like
PeterMmm
PeterMmm

Reputation: 24630

"LIKE" does not work as expected

In Postgresql 8 why this is ok

select * from prod where code like '1%'
select * from prod where code like '%1'

but this returns 0 rows (there are codes begining/ending with digit 1) 

select * from prod where code like '1%1'

Update

That happens in my current instalation:

# psql --version
psql (PostgreSQL) 8.3.7


create table a(code char(10));
CREATE TABLE
db=# insert into a values('111');
INSERT 0 1
db=# select * from a where code like '1%';
    code
------------
 111
(1 row)

db=# select * from a where code like '%1';
 code
------
(0 rows)

db=# select * from a where code like '1%1';
 code
------
(0 rows)

Update 2

It is the datatype ! With varchar it is Ok !

Thank you.

Upvotes: 4

Views: 9672

Answers (2)

anonymousen
anonymousen

Reputation: 96

Is it because the datatype is char(10)?

This means that it will always occupy 10 characters even though you just insert something shorter like "111". Therefore, if you don't use a 10-characters string with "1" at the end, "%1" and "1%1" will never match.

Upvotes: 8

Amadiere
Amadiere

Reputation: 11416

(EDIT: I had posted the following (with with an AND operator, rather than OR).

SELECT * FROM prod WHERE code LIKE '%1' OR code LIKE '1%';

If you want AND operator, the query in the question should work OK. However, if you want to use OR operator, then my above query is probably one of the better ways of doing it.

Upvotes: 1

Related Questions