Well, that certainly is interesting. Oracle and MS-SQL preserve the
trailing space when concatenating. Does anyone remember the logic for
trimming space with ||?
---------------------------------------------------------------------------
William ZHANG wrote:
>
> Bruce said:
> > How do other databases handle this?
>
> I have tried on MS SQL Server 2000 and Oracle 9i for Windows.
> SQL Server doesn't like character_length and || , so use len and + instead.
> Oracle doesn't like character_length either, use length.
> Hope the result may help.
>
> create table chartest(col char(10) not null);
> insert into chartest values ('AAA');
>
> PostgreSQL:
> select character_length(col) from chartest;
> 10
> SQL Server
> select len(col) from chartest;
> 3
> Oracle
> select length(col) from chartest;
> 10
>
> PostgreSQL:
> select character_length(col || 'hey') from chartest;
> 6
> SQL Server:
> select len(col + 'hey') from chartest;
> 13
> Oracle:
> select length(col || 'hey') from chartest;
> 13
>
> PostgreSQL:
> select 'aaa ' || 'bb';
> aaa bb
> SQL Server:
> select 'aaa ' + 'bb';
> aaa bb
> Oracle:
> select 'aaa ' || 'bb' from dual;
> aaa bb
>
> PostgreSQL:
> select cast('aa ' as char(10)) || 'b';
> aab
> SQL Server:
> select cast('aa ' as char(10)) + 'b';
> aa b
> Oracle:
> select cast('aa ' as char(10)) || 'b' from dual;
> aa b
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
