🚀 go-pugleaf

Try

Select * FROM Table1 C
where cast(getSubStr(c.PN,1, 3)||getSubStr(c.PN,5, 4) as Integer)>=1234567
and
cast(getSubStr(c.PN,1, 3)||getSubStr(c.PN,5, 4) as Integer)<=1234599

where getSubStr is a UDF to get the substring

--
Amita
Agnisoft
www.agnisoft.com
"Ronny Bengtsson" <Ronny_Bengtsson@hotmail.com> wrote in message
news:Xns92F6EC38DAE0Cronnybengtssonrobese@207.105.83.65...
> Wayne,
>
> This is what I want to do.
>
> I have a database with documents of modifications that could affect
> different part numbers that I have in my inventory.
>
> The issuer of the document could say the the modification will affect a
> range of partnumbers
> i.e. 123-4567 to 123-4599
>
> I want to check my inventory database if I have partnumbers within this
> range.
> But the partnumbers in the inventory database could be previous entered
> and saved in different ways i.e. 123/4567, 123-4568, 123 4590 etc.
>
> Thats why I have the needs for both the LIKE and BETWEEN clauses.
> to find the range of partnumbers regardless of how they are entered in
> the database as long the partnumbers have the tokens at the same place..
>
>
> In my example above my query should give me the 3 partnumbers above as
> they are in the range 123-4567 to 123-4599.
>
> /Ronny
>
>
>
> "Wayne Niddery [TeamB]" <wniddery@chaff.aci.on.ca> wrote in
> news:3e131ffe$1@newsgroups.borland.com:
>
> > "Ronny Bengtsson" <Ronny_Bengtsson@hotmail.com> wrote in message
> > news:Xns92F69DA62D2AFronnybengtssonrobese@207.105.83.65...
> >>
> >> I have needs for a SQL query that use both LIKE with wildcards % and
> >> BETWEEN.or IN
> >>
> >> Example
> >>
> >> Select * FROM Table1 C
> >> WHERE  C.PN BETWEEN(LIKE '101%381015%9', LIKE '101%381015%99')
> >
> > The question is what you are actually hoping will be returned from the
> > two LIKE clauses. Are you hoping (for example) that if the first LIKE
> > returns '101A381015B9' then the second will return '101A381015B99' (so
> > that BETWEEN works on a set of such ranges rather than one large
> > range)? If so then I don't see a way to formulate it in a single
> > statement.
> >
> > The two arguments of BETWEEN can each be a subselect (e.g: BETWEEN
> > (SELECT PN FROM...) AND (SELECT PN FROM...), but I don't think that
> > will help you here because in that form each subselect must return
> > exactly one row.
> >
> > The only way to do this in one statement is to have a whole series of
> > Betweens with very specific ranges specified instead of wild cards
> > (likely STARTING WITH could be used in this case: STARTING WITH
> > '101A381015B9'.
> >
> > Another possibility is to first prepare another table to contain the
> > ranges, e.g. it might be called RANGES and contain fields FLD1 and
> > FLD2: '101A381015B9', '101A381015B99'
> > '101G381015M9', '101G381015M99'
> > '101X381015S9', '101X381015S99'
> >
> > Now you can use a join:
> >  Select * FROM Table1 C
> >  JOIN RANGES R ON C.PN BETWEEN R.FLD1 AND R.FLD2
> >
> > --
> > Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
> > "Democracy, without that guarantee of liberty, is merely a method of
> > selecting tyrants." - Alan Nitikman
> >
> >
> >