Hello SQL Gurus,

   I have a string field which is an account # called acctnumb.

   I have a string reference field which contains several account #s separated by
a ';'.
   I have the Account #s in a string variable, cAcctNumbs, so I can assign it to
a Query parameter.

   I need the SQL for a Where statement that will test for the presence of
acctnumb in cAcctNumbs.  I thought I could do it with an 'IN' operator but
can't seem to make it work.

   Any assistance appreciated.

Regards & TIA,

Ian

--

Sorry, should have indicated.  The data from the Reference account #s field
looks like the following..   123456;234567;345678

   Let's say I have a number of records with 123456 & 234567 as well as many
other account numbers the acctnumb field.

   I need to get just those that are in the reference accounts.  Yes I could hard
code them but the reference account #s change from customer to customer.

Regards,

Ian



--

Ian


IIRC you need POS or POSITION. If your account numbers are fixed length then you'll be ok otherwise you'll end up with false positives

The easy case (fixed length) would be something along the lines of

WHERE POS(acctnumb IN cAcctNumbs) > 0

The more difficult case

WHERE
POS(acctnumb IN cAcctNumbs) > 0
AND
(
acctnumb =cAcctNumbs
OR
SUBSTRING(cAcctNumbs FROM LENGTH(acctnumb)+1 FOR 1) = ';'
)


I think


Roy Lambert

"Ian Branch" <branch@sitathome.net> wrote in
news:7FDD50A4-6CB1-42F2-B3F7-02D8B497121F@news.elevatesoft.com:

> Sorry, should have indicated.  The data from the Reference account #s
> field looks like the following..   123456;234567;345678
>
>      Let's say I have a number of records with 123456 & 234567 as well
>      as many
> other account numbers the acctnumb field.
>
>      I need to get just those that are in the reference accounts.  Yes
>      I could hard
> code them but the reference account #s change from customer to
> customer.
>
> Regards,
>
> Ian
>

what about using:

WHERE ';'+cAcctNumbs+';' LIKE '%;'+acctnumb+';%'

--
Chris