🚀 go-pugleaf

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
> 
> 
>