Re: [despammed] substr or char_length problem - Mailing list pgsql-sql

am  17.06.2005, um 13:01:15 +0300 mailte Fatih Cerit folgendes:
> Dear ALL
> 
> I have a problem with function substr or char_length or both. I guery A2 
> and it works fine. But sometimes gives 'ERROR:  negative substring length 
> not allowed'. When I test many many times with diffrent values, never gives 
> error. Sample table and query below.
> 
> 
> 
> A1              A2
> -------------------
> 1               1957
> 2               197
> 3               19
> 4
> 5               NULL
> 6               1
> 7               195
> 
> 
> Select * from tbl_xxx where 
> tbl_xxx.A2=substr('196895588454554545454',0,char_length(tbl_xxx.A2)+1);

Perhaps because char_length() returns NULL and this is a invalid value
for substr(). Use coalesce():

... substr('196895588454554545454',0,coalesce(char_length(tbl_xxx.A2),0)+1);


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===