Re: Range of caracters
От | Ragnar |
---|---|
Тема | Re: Range of caracters |
Дата | |
Msg-id | 1186510694.5778.401.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Range of caracters (Wilton Wonrath <wrwonrath@yahoo.com.br>) |
Список | 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(chassiinicialfrom '^[^0-9]*'), substr(chassiinicial from '[0-9]+$')::integer) and (substr(chassifinal from'^[^0-9]*'), substr(chassifinal from '[0-9]+$')::integer); gnari
В списке pgsql-sql по дате отправления: