CODEWITHSUNDEEP
sqlpostgresql
JohnDoe
JohnDoe

Reputation: 2513

Dynamically build SQL and statement

How can I write the following SQL query:

'Select *
from Address
where cityname like %1
and zipcode = %2'

when zipcode is optional?

Zipcode is optained from a urlquery. So it might be empty or likely a valid zip code.

If it is empty (''), the query should return all rows, if it is not empty, it should work as an additional 'and' parameter to the query.

Upvotes: 0

Views: 56

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

SELECT *
FROM address
WHERE cityname LIKE %1
  AND zipcode = COALESCE(NULLIF(%2, ''), zipcode);

If %2 is empty, NULLIF will return NULL, COALESCE will return zipcode and the condition will evaluate to TRUE.

Upvotes: 1

Related Questions