Re: [despammed] substr or char_length problem

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