Re: PG SQL and LIKE clause

On 9/13/19 9:14 AM, Adrian Klaver wrote:
> 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.

This is highly dependent on implementation.  On the RDBMS that I used to
work on, trailing whitespace was automatically stripped from CHAR(xx) fields.

--
Angular momentum makes the world go 'round.