Re: PG SQL and LIKE clause

On 9/12/19 11:11 PM, Ron wrote:
> 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%'

The above is if you want to find a string starting with 'Ali'. If you 
are looking for the complete string 'Ali' then it is appropriate. The OP 
is looking for a way to automatically match a complete string against a 
right stripped string from a CHAR field.

> 
>> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com