CODEWITHSUNDEEP
sqloracle-database
Alex
Alex

Reputation: 15708

Oracle where clause that matches all entries

Is there a way to construct a parametrised query with a where clause on a field that matches all entries in the field. For example I am imagining syntax like

'select brands from cars where brands = param'

such that if param = 'Toyota' then it only returns rows matching Toyota, but if param = * it returns all rows from the cars table.

Upvotes: 0

Views: 495

Answers (5)

The Fabio
The Fabio

Reputation: 6250

The proper way to return all rows regardless of brand is to remove the brands = param from your filter, not to use a wildcards or functions on it...

It is common to add where 1=1 to clauses in your case and use in the following pattern:

If param has a value

select brands from cars where 1=1 and brands = param

otherwise you just remove the and brands = param part:

select brands from cars where 1=1

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select * from cars where brands like '%'||parameter||'%'

You can use distinct to get all brands from your table.

Upvotes: 2

Nick.Mc
Nick.Mc

Reputation: 19184

The usual workaround is 

select brands from cars where brands LIKE param

If param = 'Toyota' it returns only Toyota

If param = '%' it returns everything

Simple but not necessarily performant.

Upvotes: 2

sqluser
sqluser

Reputation: 5672

Try this. It's a conditional WHERE clause

WHERE brands = NVL(param, brands)

Means if the param is NULL, use brands = brands

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you have a variable, you would do:

where v_brand is null or v_brand = brand

If you just want an expression that matches everything:

where 1 = 1

Upvotes: 1

Related Questions