SQL Parameter to Include All on ID Column

user_3464777 0
Viewed 202

I'm just taking a look at the following query

select * from tablename
where id like '%%';

So that it can handle parameters to include all of the data or filtered data like bellow

select * from tablename
where id like '%1%';

Which is fine for most parameters I use but this seems wrong for an ID because it will return all data that has IDs containing 1 which I don't want

To get around this I can only append the where clause if the ID is given but that seems like a pain in the butt

Is it possible to use a different type of where clause so that a wildcard can be used in a where equals clause instead of a where like clause, example

select * from tablename
where id = '*';

So that the same query can be used to return all or filtered data? Pass parameter '*' for all or parameter '1' for ID 1 specifically

(I'm not sure if it matters for this case but I'm using PostgreSQL 9.6.12 in this example)

where-clause parameters postgresql sql
1 Answers

This would often be expressed as:

where (id = :id or :id is null)

null is the "magic" value that represents all rows.

user_11440350
Related