Clever Geek Handbook

"LIKE" doesn't work as expected

In Postgresql 8 why is this ok

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

but this returns 0 lines (there are codes starting / ending with digit 1)

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

Update

This is happening in my current installation:

# 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

This is a data type! With varchar it's Ok!

Thank.

+2


source to share


2 answers


Is it because the data type is char (10)?



This means that it will always be 10 characters long, even if you just insert something shorter like "111". Therefore, unless you use a 10 character string with "1" at the end, "% 1" and "1% 1" will never match.

+7


source


(EDIT: I posted the following (with the AND operator, not the OR).

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


If you want the AND operator, the query in the question should work fine. However, if you want to use the OR operator, my above query is probably one of the best ways to do it.

+1


source






More articles:


All Articles