CODEWITHSUNDEEP
sqlsql-likecase-statementopenedgeprogress-db
Tarski
Tarski

Reputation: 5500

LIKE operator for Progress DB SQL

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

Upvotes: 3

Views: 18747

Answers (4)

Niklas
Niklas

Reputation: 11

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

Upvotes: 1

Tom Bascom
Tom Bascom

Reputation: 14020

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.

Upvotes: 0

David Webb
David Webb

Reputation: 193724

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

Upvotes: 6

pedromarce
pedromarce

Reputation: 5669

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

Upvotes: 0

Related Questions