How to CAST a value in PostgreSQL for use in WHERE with LIKE statement? -

How to CAST a value in PostgreSQL for use in WHERE with LIKE statement? -

i'm trying prepare sql query later convert doctrine2 dql since it's part of symfony2 project. ddl has:

create table "nomencladores"."norma" ( "id" int4 not null, "comite_tecnico_id" int4, "numero" varchar (10) collate "default" not null, "anno" int4 not null, "nombre" varchar (255) collate "default" not null, "activo" bool, constraint "norma_pkey" primary key ("id"), constraint "fk_f00cbe8e84edad75" foreign key ("comite_tecnico_id") references "nomencladores"."comite_tecnico" ("id") on delete no action on update no action ) (oids = false);

and i'm trying execute query find %45% , i've tried queries without success:

the 1 generated doctrine2 in dql

select n0_.numero numero0, n0_.anno anno1, n0_. id id2, n0_.nombre nombre3, n0_.activo activo4, n0_.comite_tecnico_id comite_tecnico_id5 nomencladores.norma n0_ n0_.anno %45%;

trying cast values

select n0_.numero numero0, n0_.anno anno1, n0_. id id2, n0_.nombre nombre3, n0_.activo activo4, n0_.comite_tecnico_id comite_tecnico_id5 nomencladores.norma n0_ cast (n0_.anno char) %45%; select n0_.numero numero0, n0_.anno anno1, n0_. id id2, n0_.nombre nombre3, n0_.activo activo4, n0_.comite_tecnico_id comite_tecnico_id5 nomencladores.norma n0_ cast (n0_.anno, "fm9999") %45% select n0_.numero numero0, n0_.anno anno1, n0_. id id2, n0_.nombre nombre3, n0_.activo activo4, n0_.comite_tecnico_id comite_tecnico_id5 nomencladores.norma n0_ to_char(n0_.anno, "fm9999") %45% select n0_.numero numero0, n0_.anno anno1, n0_. id id2, n0_.nombre nombre3, n0_.activo activo4, n0_.comite_tecnico_id comite_tecnico_id5 nomencladores.norma n0_ n0_.anno::text "%45%"

and none works, right way accomplish on postgresql?

the syntax be:

where n0_.anno::text '%45%';

you need cast number text (or varchar) before can utilize like operator. right hand argument like text value. input string literal precise. need single quotes values, double quotes identifiers.

if anno supposed hold year , interested in last 2 digits, create that:

where n0_.anno::text '%45';

or better, yet:

where n0_.anno % 100 = 45;

% beingness modulo operator. (not related % symbol in like patterns!) 45 (without quotes) beingness numeric constant.

postgresql postgresql-9.2 casting