Re: PG SQL and LIKE clause

On 9/13/19 7:35 AM, Ron wrote:
> 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.
>

That is what is driving the OP's question. On Sybase the trailing
whitespace is stripped apparently, on Postgres it is not, at least for
the purposes of LIKE. Though for other comparisons whitespaces are
ignored. See below for more information:

https://www.postgresql.org/docs/11/datatype-character.html

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com