Hello,
I was trying to use an expression on the right hand side of the like operator:
select * from incident where naam like '040' || '002'
This returns this error:
Syntax Error: Can't confirm whether (naam='040')||'002' is a boolean expression.
When I try to use parantheses like this:
select * from incident where naam like ('040' || '002')
I get this error:
Syntax error: Stopped parse at ((id12)
Are expressions supported for the LIKE-operator? In for instance PostgreSQL it is allowed.
With kind regards,
Remco Schoen
|
>Are expressions supported for the LIKE-operator? I
[NOT] LIKE pattern {escape 'escape_character'},[NOT] ILIKE pattern {escape 'escape_character'}
pattern should be a string value, upper(str),lower(str), or ? paramter. HXTT DBF can utilize index file to quicken some like expression.
|| operator: left string concat right string
I don't know whether PostgreSQL allow variable for pattern. Mabye PostgreSQL allows only string constant operation?
|
I tested the statement:
select * from incident where naam like '040' || '002'
in :
* Oracle
* SQL Server
* PostgreSQL
* H2
and all these databases seam to evaluate the expression '040' || '002' to the string '040002' and use that as the pattern for the like.
|
We can provide such a support. BTW, please list other patterns which you wish to support too,
|
>select * from incident where naam like '040' || '002'
>select * from incident where naam like ('040' || '002')
Supported now. BTW, please list other patterns which you wish to support too,
|
Okay, thanks for supporting this!
We don't have more patterns, that I know off now. We mainly use it like this:
like '%' || 'searchvalue'
and
like '%' || 'searchvalue' || '%'
We use these as database-independent alternatives for functions like starts with (LEFT() = ) and contains (AT).
|
HXTT DBF supports also LEFT function and $(check whether left string is contained in right string).
|
We know that you support these functions, your database is in our case not a problem.
But for instance Oracle doesn't support the LEFT-function and you could use the SUBSTRING-function in that case. MS SQL Server then doesn't support the SUBSTRING-function but the SUBSTR-function.
That's why we went for the like-option.
|