LIKE operator for Progress DB SQL

Question

I'm trying to do something like this in Progress SQL (THIS IS NOT POSTGRES!)

SELECT
    CASE WHEN code LIKE '%foo%' THEN 'Y' ELSE 'N' END as foo
FROM
    bar

However Progress does not support a LIKE operator. INSTR looks like it might do the job, but it is a Progress extension an isn't supported on the DB I am using. Is there another way of achieving this using standard ODBC functions?

Thanks

Solution 1

There is no LIKE operator in the Progress 4GL. (There is a LIKE keyword, but it is used for something different.) Instead you need to use MATCHES or CONTAINS operators. I've never used a SQL interface to Progress but it may be the same.

So you could try:

SELECT
    CASE WHEN code MATCHES '*foo*' THEN 'Y' ELSE 'N' END as foo
FROM
    bar

Note - MATCHES uses * for a wildcard instead of %.

Or:

SELECT
    CASE WHEN code CONTAINS 'foo' THEN 'Y' ELSE 'N' END as foo
FROM
    bar

Solution 2

Just found this question and to anybody who might still have a problem with it i suggest using SquirrelSQL Client, which allows you to write almost 100% SQL syntax towards the Progress base. Like included