Re: PG SQL and LIKE clause

On 9/13/19 12:28 AM, Matthias Apitz wrote:
> Hello,
>
> We're porting a huge Library Management System, written using all kind
> of languages one can think of (C, C++, ESQL/C, Perl, Java, ...) on Linux
> from the DBS Sybase to PG, millions of lines of code, which works also
> with DBS Oracle and in the past with INFORMIX-SE and -ONLINE.
>
> We got to know that in CHAR columns with trailing blanks a
>
> SELECT ... FROM ... WHERE name LIKE 'Ali'
>
> does not match in 'name' having 'Ali '.

Did you forget the "%"?  Because the SQL standard which PostgreSQL follows is:

SELECT ... FROM ... WHERE name LIKE 'Ali%'

> I glanced through our code with grep pipelines and found some hundred
> places which would be affected by this problem. I'm not interested in a
> religious discussion if or if not this behaviour of PG is correcter or
> better than in Sybase. It's just different to Sybase.

SQL Server derives from Sybase, and it would also fail on this statement.

> Any hints to address this problem? Or is there any compile time option
> for the PG server to address this?
>
> Thanks
>
> matthias

--
Angular momentum makes the world go 'round.