From wrwonrath@yahoo.com.br Tue Aug  7 17:14:26 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 96AFE9FBBA2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:14:26 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 43190-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:14:23 -0300 (ADT)
X-Greylist: delayed 00:06:39.184378 by SQLgrey-1.7.5
Received: from web34314.mail.mud.yahoo.com (web34314.mail.mud.yahoo.com
	[66.163.178.146])
	by postgresql.org (Postfix) with SMTP id C57EF9FBBEC
	for <pgsql-sql@postgresql.org>; Tue,  7 Aug 2007 14:14:22 -0300 (ADT)
Received: (qmail 54481 invoked by uid 60001); 7 Aug 2007 17:07:42 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=1IBbvi4Fn+FJj2xP+in+zUW1E2dvbtvf+4tRdYqI5ULJxIcy0PLhLkHj0sqnSMxME/phdpc5J/YI3lxBV2wMc0GEUT6re6pK25iK1ika2psTi6j02F2qV2Z7fRGu8Iml2oExYbGty3arO+Iu3vW5DJoBdhMIlRbDsfHOlpdhAQY=;
X-YMail-OSG: 
 _G5Vx6YVM1lAz6fgvyA1LSQVxQanv0lIzosvCE.D24wrgS6u4fuQXwLsWAUSNLIaxQ6OT1Xq1XWKIc4R3CFOqSD.bW0c2k5NErcl0orGnFd_ht1YdVsVQlAO62jfLA--
Received: from [201.62.123.38] by web34314.mail.mud.yahoo.com via HTTP;
	Tue, 07 Aug 2007 10:07:42 PDT
X-Mailer: YahooMailRC/651.41 YahooMailWebService/0.7.119
Date: Tue, 7 Aug 2007 10:07:42 -0700 (PDT)
From: Wilton Wonrath <wrwonrath@yahoo.com.br>
Subject: Range of caracters
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-463102255-1186506462=:54350"
Message-ID: <603019.54350.qm@web34314.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200708/38
X-Sequence-Number: 28976

--0-463102255-1186506462=:54350
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hello,=0A=0AI have a doubt about a query using a range of caracters. Exampl=
e:=0A=0A    Data Table=0A=0AInicial                              ----------=
---  Final=0A9C2HB02107R008000                         9C2HB02107R008200   =
         =0AFAIXA1                                                FAIXA100=
=0A=0AI=B4m doing the following query:=0A=0ASELECT recallfxchassi.* FROM re=
callfxchassi =0ALEFT JOIN recall ON recallfxchassi.idrecall =3D recall.id W=
HERE =0A('FAIXA2' BETWEEN chassiinicial AND chassifinal);=0A=0AThis is not =
returning anything. I believe that is because it=B4s check=B4s 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).=0A=0ADoes anyone =
knows if exists some way or command in PostgreSQL that could solve this "pr=
oblem" ?=0A=0AThanks.=0A=0A=0A=0A=0A      Alertas do Yahoo! Mail em seu cel=
ular. Saiba mais em http://br.mobile.yahoo.com/mailalertas/
--0-463102255-1186506462=:54350
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:times new roman, new york, times, serif;=
font-size:12pt"><div>Hello,<br><br>I have a doubt about a query using a ran=
ge of caracters. Example:<br><br>&nbsp;&nbsp;&nbsp; Data Table<br><a href=
=3D"mailto:pgsql-sql@postgresql.org"><br>Inicial&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&n=
bsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -------------&nbsp; Final<b=
r>9C2HB02107R008000&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; 9C2HB0=
2107R008200&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; <br>FAI=
XA1&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nb=
sp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&n=
bsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
 &nbsp;&nbsp;&nbsp; FAIXA100<br></a><br>I=B4m doing the following query:<br=
><a href=3D"mailto:pgsql-sql@postgresql.org"><br>SELECT recallfxchassi.* FR=
OM recallfxchassi <br>LEFT JOIN recall ON recallfxchassi.idrecall =3D recal=
l.id WHERE <br>('FAIXA2' BETWEEN chassiinicial AND chassifinal);<br></a><br=
>This is not returning anything. I believe that is because it=B4s check=B4s=
 caracter by caracter and 2 is bigger than 1. But in a real situation&nbsp;=
 I will need that return something (FAIXA2 is between FAIXA1 and FAIXA100).=
<br><br>Does anyone knows if exists some way or command in PostgreSQL that =
could solve this "problem" ?<br><br>Thanks.<a href=3D"mailto:pgsql-sql@post=
gresql.org"><br></a></div></div><br>=0A=0A=0A      Alertas do Yahoo! Mail e=
m seu celular. <a href=3D"http://br.mobile.yahoo.com/mailalertas/">Saiba ma=
is</a>.=0A</body></html>
--0-463102255-1186506462=:54350--



From gnari@hive.is Tue Aug  7 18:10:06 2007
Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id F143D9FBDA7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 15:10:06 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 35524-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 15:09:58 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mx2.ipf.is (jeep.ipf.is [85.197.192.148])
	by postgresql.org (Postfix) with ESMTP id 261A99FB7E3
	for <pgsql-sql@postgresql.org>; Tue,  7 Aug 2007 15:09:58 -0300 (ADT)
Received: (qmail 15780 invoked by uid 1010); 7 Aug 2007 18:09:34 +0000
Received: from 88.149.106.19 by jeep (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 3.370768 secs); 07 Aug 2007 18:09:34 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via jeep
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 3.370768 secs Process 15761)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx2.ipf.is with SMTP; 7 Aug 2007 18:09:30 +0000
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
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/41
X-Sequence-Number: 28979

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