PostgreSQL query, LIKE operator

Question

The documentation tells me LIKE and NOT LIKE are used in SQL to search if cells contain/starts/ends with certain characters.

I'm trying to use this in PostgreSQL which tells me the operator does not exist. My query:

SELECT * FROM "City"."models" 
WHERE (("City"."models".identifier NOT LIKE'%$%') 
AND
     (("City"."models".id LIKE '%2%') 
     OR 
     ("City"."models".id LIKE '%1%'))) 

ORDER BY "City"."models".town_id ASC LIMIT 10

Column types:

identifier->uuid
id->int
town_id->int

Where is my mistake?

Show us some sample data, preferably in a SQLFiddle and also post your query there — Radu Gheorghiu
– Radu Gheorghiu, Commented Oct 7, 2015 at 8:07
My guess is "identifier" and "id" are not strings. LIKE won't work with numeric values. — CFreitas
– CFreitas, Commented Oct 7, 2015 at 8:12
LIKE is not applicable to integers, you'll have to use something like CAST(id as VARCHAR) LIKE '%2%' etc. But that looks weird in general, why do you want to filter integers like text? — bereal
– bereal, Commented Oct 7, 2015 at 8:12
it looks weird and i'm sure the error message tells him exactly that int does not work with LIKE — A ツ
– A ツ, Commented Oct 7, 2015 at 8:14

CFreitas · Accepted Answer · 2015-10-07 08:15:16Z

4

Try this:

SELECT * FROM "City"."models" 
WHERE (("City"."models".identifier::varchar NOT LIKE'%$%') 
AND
     (("City"."models".id::varchar LIKE '%2%') 
     OR 
     ("City"."models".id::varchar LIKE '%1%'))) 

ORDER BY "City"."models".town_id ASC LIMIT 10

answered Oct 7, 2015 at 8:15

CFreitas

1,79520 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL query, LIKE operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related