🚀 go-pugleaf

"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