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

Question

I'm trying to fix a SQL query for later convert it to Doctrine2 DQL since it's part of a Symfony2 project. This is what my 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
) WITH (OIDS = FALSE);

And I'm trying to execute a LIKE query to find %45% and I've tried all this queries without success:

The one generated by Doctrine2 in DQL

SELECT
    n0_.numero AS numero0,
    n0_.anno AS anno1,
    n0_. ID AS id2,
    n0_.nombre AS nombre3,
    n0_.activo AS activo4,
    n0_.comite_tecnico_id AS comite_tecnico_id5
FROM
    nomencladores.norma n0_
WHERE
    n0_.anno LIKE %45%;

Trying to cast the values

SELECT
    n0_.numero AS numero0,
    n0_.anno AS anno1,
    n0_. ID AS id2,
    n0_.nombre AS nombre3,
    n0_.activo AS activo4,
    n0_.comite_tecnico_id AS comite_tecnico_id5
FROM
    nomencladores.norma n0_
WHERE
    CAST (n0_.anno AS CHAR) LIKE %45%;

---

SELECT
    n0_.numero AS numero0,
    n0_.anno AS anno1,
    n0_. ID AS id2,
    n0_.nombre AS nombre3,
    n0_.activo AS activo4,
    n0_.comite_tecnico_id AS comite_tecnico_id5
FROM
    nomencladores.norma n0_
WHERE
    CAST (n0_.anno, "FM9999") LIKE %45%

---

SELECT
    n0_.numero AS numero0,
    n0_.anno AS anno1,
    n0_. ID AS id2,
    n0_.nombre AS nombre3,
    n0_.activo AS activo4,
    n0_.comite_tecnico_id AS comite_tecnico_id5
FROM
    nomencladores.norma n0_
WHERE
    to_char(n0_.anno, "FM9999") LIKE %45%

---

SELECT
    n0_.numero AS numero0,
    n0_.anno AS anno1,
    n0_. ID AS id2,
    n0_.nombre AS nombre3,
    n0_.activo AS activo4,
    n0_.comite_tecnico_id AS comite_tecnico_id5
FROM
    nomencladores.norma n0_
WHERE
    n0_.anno::text LIKE "%45%"

And none works, what is the right way to achieve this on PostgreSQL?

Answer 1

The syntax could be:

WHERE n0_.anno::text LIKE '%45%';

You need to cast the number to text (or varchar) before you can use it with the LIKE operator.
The right hand argument for LIKE is a text value. Your input is a string literal to be precise. You need single quotes for values, double quotes are for identifiers.

If anno is supposed to hold a year and you are just interested in the last two digits, make that:

WHERE n0_.anno::text LIKE '%45';

Or better, yet:

WHERE n0_.anno % 100 = 45;

% being the modulo operator. (Not related to the % symbol in LIKE patterns!)
45 (without quotes) being a numeric constant.