SQL/ASP: Split and SELECT ?

[ElEye]

Greetings!

The column [ColumnC] contains multiple values. The values are comma separated numbers. My variable is MyID.

Example record1:
ColumnA: Hello
ColumnB: Excellent
ColumnC: 1, 15, 23, 35

Example record2:
ColumnA: Hi there
ColumnB: Fabulous
ColumnC: 12, 33, 105

The SQL example:
StrSQL = "SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnC LIKE " & MyID

I'd like to make a SELECT that will display all the records that contain MyID.
If MyID = 1 then I'd like to only find record1, not record2 (record2 has a 1 in the 12.)

I use SPLIT to display individual MyIDs that are found in ColumnC, but I'm stymied as how to use it in a SELECT.

TIA!

Dave
[

http://flashspot.com

]

 

[TheCandyman]

My thoughts are this:

if you are having problems with it finding the 1 in '15' then you may need something like: if (Asc(MyID) = (Asc(rs("ColumnC")))) then or compare to an array if that's what your using. No mistakes on that kind of compare.

As far as the SQL, i don't know if there is one or not that will break it. You have to to post that in the SQL forms.

 

[vongrunt]

Problem is about ColumnC - it is not atomic (hard to search) and contains repeatable values (already violated 1NF rule).

Theoretical ramblings aside, you can try something like:

myID = 3
strSQL = "SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ', ' + ColumnC + ', ' LIKE '%, " & myID & ",%'"

The trick is to make comma-separated list uniform for searching. So if you want to search for 3 query will actually search for ", 3,".

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)