pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRange of caracters
2+ messages / 2 participants
[nested] [flat]
* Range of caracters
@ 2007-08-07 17:07 Wilton Wonrath <wrwonrath@yahoo.com.br>
0 siblings, 1 reply; 2+ messages in thread
From: Wilton Wonrath @ 2007-08-07 17:07 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a doubt about a query using a range of caracters. Example:
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" ?
Thanks.
Alertas do Yahoo! Mail em seu celular. Saiba mais em http://br.mobile.yahoo.com/mailalertas/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Range of caracters
@ 2007-08-07 18:18 Ragnar <gnari@hive.is>
parent: Wilton Wonrath <wrwonrath@yahoo.com.br>
0 siblings, 0 replies; 2+ messages in thread
From: Ragnar @ 2007-08-07 18:18 UTC (permalink / raw)
To: Wilton Wonrath <wrwonrath@yahoo.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-08-07 18:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-07 17:07 Range of caracters Wilton Wonrath <wrwonrath@yahoo.com.br>
2007-08-07 18:18 ` Ragnar <gnari@hive.is>
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