CODEWITHSUNDEEP
sqlpostgresqlenums
cfatt10
cfatt10

Reputation: 788

Using ILIKE on an ENUM in SQL

On a web frontend search, I would like users to be able to type 'wed', 'wednesday', 'Wed', etc. to select the day of the week. Given the enum set for days of the week, ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), is it possible to use an ILIKE comparison on the values (WHERE day ILIKE x)? I haven't been able to make anything work and I haven't found anything through google.

Upvotes: 6

Views: 3134

Answers (1)

BlindAndFurious
BlindAndFurious

Reputation: 758

Try casting to text:

SELECT * FROM table_name where enum_column::text ILIKE 'Mon%';

Full example:

CREATE TYPE DAY AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

CREATE TABLE test (t1 DAY);

INSERT INTO test VALUES ('Monday')

SELECT * FROM test where t1::text ILIKE 'Mon%';

Upvotes: 13

Related Questions