From: Andreas Kretschmer <akretschmer@despammed.com>
To: pgsql-sql@postgresql.org
Subject: Re: [despammed] substr or char_length problem
Date: Fri, 17 Jun 2005 12:25:28 +0200
Message-ID: <20050617102528.GD15166@webserv.wug-glas.de> (raw)
In-Reply-To: <014a01c57323$90b72b20$b8e58bd5@cerit>
References: <014a01c57323$90b72b20$b8e58bd5@cerit>
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 ===
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@despammed.com
Subject: Re: [despammed] substr or char_length problem
In-Reply-To: <20050617102528.GD15166@webserv.wug-glas.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox