I think a simple "like" will give you false matches - %1% can match1, 11,
121, etc. This is more complicated but I believe it will work:
/*
create table test_match (the_key int, the_sought varchar(50))
create table test_find (a_key int, finder varchar(50))
insert into test_match values (1, '1,2,3,4,10,11')
insert into test_match values (2, '1,2,3,4,12')
insert into test_find values (1, '10')
insert into test_find values (2, '12')
*/
select test_match.the_key, test_find.a_key
from test_match, test_find
where (patindex('%,' + test_find.finder + ',%', test_match.the_sought)
+ patindex(test_find.finder + ',%', test_match.the_sought)
+ patindex('%,' + test_find.finder, test_match.the_sought)) > 0
or of course you could normalize as others have recommended.
-----Original Message-----
From: Dave Hannum [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 12, 2000 8:30 AM
To: CF-Talk
Subject: Re: SQL question
It's in an MS SQL Server 7
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 12, 2000 8:30 AM
Subject: RE: SQL question
There's no nice way that I know of (putting a list in a field is a
relational database no-no); the specific kludge would depend on what sort of
database you're using.
-----Original Message-----
From: Dave Hannum [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 12, 2000 7:33 AM
To: CF-Talk
Subject: OT: SQL question
Whew . . . I'm still in one piece after that wind last night . . .
What's the best way to query a column in a table that has a comma delimited
list?
For example, in the query below: If B_xRef is a comma delimited list,
what's the most effecient way to find all the Table_b rows that contain the
value of A_xRef (which is a single value)
SELECT A.A_xRef, B.B_xRef
FROM Table_a A, Table_b B
WHERE A.A_xRef IN B.B_xRef (or something to this effect?)
Thanks,
Dave
=================================
"What we need is a list of specific unknown problems we will encounter"
David Hannum
Web Analyst/Programmer
Ohio University
[EMAIL PROTECTED]
(740) 597-2524
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
