How to write a function only used in the where clause that evaluates to true without a comparison operator?

Basically, oracle has a boolean datatype only for PLSQL. So, as long as you stay in plsql you can use them but not in SQL.

From documentation:

Because SQL has no data type equivalent to BOOLEAN, you cannot:

• Assign a BOOLEAN value to a database table column

• Select or fetch the value of a database table column into a BOOLEAN variable

• Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement

If you want to find metadata about built-in functions, then maybe this post can help.

SQL Doesn't work like that. the where statement always looks for a function/column where something is. even if the function works you still have to tell the where statement which value you want True or False

I haven't used Oracle SQL, but looking at what you have there I think that if you write it like this

  select *  from table t  where is_prod = True

it will work, if you change the Variable type in your function to something like a Varchar(5) or something similar.

you are actually asking that function to look at several records, so when you have it like you do it acts like a Select Statement and not like a where statement. it will give the value of the function but not filter the where.it will look like a column with true or false values.

When you use the function in a Where statement like this:

SELECT *FROM table tWHERE is_Prod

it's like saying:

SELECT *FROM table tWHERE Column1

you have to clarify for the WHERE Statement

SELECT *FROM table tWHERE Column1 = 'blue' or is_Prod = 'false'

in C# you can use a String as a boolean, if it is null it returns false

in SQL Server it comes out like this

Column2 IS NULL

you still need an operator

* Separator *

as I don't use Oracle I was unable to test this.

http://docs.oracle.com/cd/B14117_01/server.101/b10759/conditions018.htm

REGEXP_LIKE is a like Statement. so it uses a comparison operator.

you could probably write a regexp in a like statement, although I am sure that it is time consuming and monotonous so they made a function that does it for you.

in other words you still have to use the '= whatever' on the function that you created.