Subject: Re: Range of caracters
From: Ragnar <gnari@hive.is>
To: Wilton Wonrath <wrwonrath@yahoo.com.br>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <603019.54350.qm@web34314.mail.mud.yahoo.com>
References: <603019.54350.qm@web34314.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Tue, 07 Aug 2007 18:18:14 +0000
Message-Id: <1186510694.5778.401.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

On žri, 2007-08-07 at 10:07 -0700, Wilton Wonrath wrote:

>     Data Table
> 
> Inicial                              -------------  Final
> 9C2HB02107R008000                         9C2HB02107R008200
>     
> FAIXA1                                                FAIXA100
> 
> I“m doing the following query:
> 
> SELECT recallfxchassi.* FROM recallfxchassi 
> LEFT JOIN recall ON recallfxchassi.idrecall = recall.id WHERE 
> ('FAIXA2' BETWEEN chassiinicial AND chassifinal);
> 
> This is not returning anything. I believe that is because it“s check“s
> caracter by caracter and 2 is bigger than 1. But in a real situation
> I will need that return something (FAIXA2 is between FAIXA1 and
> FAIXA100).
> 
> Does anyone knows if exists some way or command in PostgreSQL that
> could solve this "problem" ?

sounds like you need to normalize your schema, but it is possible.

if the prefix is fixed then you can do 
WHERE substr('FAIXA2',6)::integer 
      between substr(chassiinicial,6)::integer 
      and substr(chassifinal,6)::integer;


if the prefix is not fixed, but it's length is, and you are using a
sufficiently recent version of postgresql, you can do:
WHERE (substr('FAIXA2',1,5),
       substr('FAIXA2',6)::integer) 
   between    
       (substr(chassiinicial,1,5),
        substr(chassiinicial,6)::integer) 
   and (substr(chassifinal,1,5),
        substr(chassifinal,6)::integer);

if the prefix length is not fixed, you will have
to do some juggling:
WHERE (substring('FAIXA2' from '^[^0-9]*'),
       substring('FAIXA2' from '[0-9]+$')::integer) 
   between    
       (substr(chassiinicial from '^[^0-9]*'),
        substr(chassiinicial from '[0-9]+$')::integer) 
   and (substr(chassifinal from '^[^0-9]*'),
        substr(chassifinal from '[0-9]+$')::integer);


gnari