G'day all. This one has me stumped.

I have a field in my database where I store different responses the
user makes based on their settings. The data in the field looks
something like this;

"1,2,3;4,5,6;7,8,9;10,11,12"
";5;;11"
"2;4,5,6;7,9;11"
"1;;7,8;"

It is basically a comma delimted field with the exception that I use
the semicolon to separate groups.

Ok heres my problem; I want to select all the records that contain say
element "1".

You might say easy just use "select * from table where field like
'*1*'" but this returns records that contain "1,10,11,12".

The other thing to think of here is that each group may contain any
number of elements (including none). Each element has its own unquie
number.

Thanks again for your help.

Have you considered using a relational design, instead of stuffing all that
data into a flat string?

Anyway, you can do this:

n = "1"
sql = "SELECT column FROM table WHERE " & _
    " ','+column+',' LIKE '%," & n & ",%'

This makes sure there is a leading and trailing comma, and makes sure that
the entire number is within commas.

See http://tinyurl.com/2wx4 for some other approaches.

Followup set to microsoft.public.inetserver.asp.db

Close. But what about the elements that are at the start or end of a
group. ie;

"1,2,3;4,5,6;7,8,9;10,11,12"

Elements 3,4,6,7,9,10 will not work with this solution.

I did consider a relational design, but the settings are database
driven - based on an unknown number of posibilities.

n = "1"
sql = "SELECT column FROM table WHERE " & _
     " ','+REPLACE(column, ';', ',')+',' LIKE '%," & n & ",%'

Followups set AGAIN to asp.db only.

Unfortunately, and this is not a criticism at all, it is poor data design.

The scenario you described is the perfect example of what databases can -
and often do - handle with ease.

To give an illustration: I often find myself in need of a data structure
that will accomodate an unknown amount of categories, with an unknown amount
of attributes and an unknown amount of ... and etc ... with each user being
presented an unknown combination, which certainly has to be stored and
retrieved at some point. This is handled through a perfect relational
database. So it can be done.

What you lack - and again no criticism is intended - is that small step to
knowing *how*. I am sure, if shown, you'd pick it up in no time and would
take that mental programming step to the next level that we all experience
from time to time.

A simple golden rule of thumb - seriously - is if you are writing code to
extract and manipulate data, to accomodate for the way the data is stored,
then the data is stored incorrectly. Period.

What you are doing here is now trying to find a solution for what shouldn't
be a problem, so I strongly recommend that after this you seriously explore
the possibility of reading about relational data; particularly parent-child
tables (or header/detail tables).

The leading and trailing characters are not a problem - a little lateral
thinking solves that - but you ahve further compounded your problem by
storing groups as well as ids in that field. Honestly, that's awful. The sql
server group would tear you apart for that :p Especially Joe Celko.

Much better would have been:
UserID    GroupID    ID
234            1             1
234            1             2
234            1             3
234            2             4
234            2             5
234            2             6
234            3             7
234            3             8
234            3             9
234            4             10
234            4             11
234            4             12

You can now extract from that table, write into it, or use it as any part of
any query and know exactly what you needed to know. I could inner join that
onto the member table on userid with a criteria of the current logged in
user, and now I'd know exactly what my id list should look like. That would
have been waaaaaaaaaaaaaay more simple and logical.

One thing you have omitted to tell us is what database you are using: If it
were sql server 2000, for instance, there would be tools at our disposal for
fixing this very easily (I have a stored procedure that will split this
column into a temp table for you in a jiffy, which could then be used in as
part of a query). Perhaps, if your database supports user defined functions,
one could be utilized here. It's impossible to say without knowing what
backend you have.

One possible - but horrible - solution is:

"1,2,3;4,5,6;7,8,9;10,11,12"

SELECT * FROM tblWhatever WHERE
    ',' & fldID & ',' LIKE '%," & n & ",%' OR fldID LIKE '%;" & n & ",%' OR
fldID LIKE '%," & n & ";%'

',' & fldID & ',' may not work. Untested.

You could also look into WHERE fldID IN yourField
If 'IN' supports a delimeter, then you could look on commas and semi-colons.
WHERE ',' + id + ',' IN field OR ';' + id ',' IN field OR ... and etc.

Additionally, consider a routine to extract this string fromt he database,
split it, and then create a temp table. Now do a new select with this temp
table as part of the query (inner join) and afterwards delete the table.
Slower, but it would work nicely.

Again, knowing your database will help enormously. If you have sql server
2000, I can be more specific on syntax and even give you a guaranteed
working solution. If you're using something else, then one of the above
solutions would do it. No code is to be used 'as-is', it's purely conceptual
stuff for you to adapt to your needs.

This post has probably seemed a tad arrogant and pompous - I apologize for
that, not intended. None of us like to be told that something we have done
sucks. But, seriously, data-design wise this does. If in any doubt on this,
I encourage you to post your dilemma in the sql server programming group for
the attention of Joe Celko (a very helpful, thorough sql author). He'll
leave you in no doubt.

I hope this helps,

~ Jen

If any part of my above post was taken as pompous and arrogant, I apologize.
Sometimes it can be hard to find the correct words and tone to impress upon
someone something that is of a slightly negative nature.

If I ever write and offend, believe me, I can be contacted on such a matter
and will - typically - offer profuse apologies.

If any offense was taken to any or all of what I said, I apologize for that.