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

Answer 1

You can try with MATCHES (same syntax than LIKE) and maybe that works.

Answer 2

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

Answer 3

What version of Progress are you using 10.0? 10.1? 10.2?

Are you using the embedded SQL-89 from within a 4GL session or are you using SQL-92 via an ODBC/JDBC connection? It sounds like you are using SQL-92 since you made reference to "drivers" and INSTR.

In any event there is no LIKE for SQL. There is INSTR and LOCATE which might do what you want though.

MATCHES is only available as a 4GL keyword. To use it in SQL you would need to be using embedded SQL-89 but nobody in their right mind would code SQL inside a 4GL session. It is only there as a marketing checkbox and it is a completely untenable way to write 4GL code.