Re: Querying a comma separated list
It works perfectly. Thank you, Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359216 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Querying a comma separated list
What I have seen done in these situations is to search where = the value or one of several like statements. where field = 'value' or field like 'value,%' or field like '%,value,%' or field like '%,value' David Phelan Web Developer IT Security & Web Technologies Emerging Health Montefiore Information Technology 3 Odell Plaza, Yonkers, NY 10701 914-457-6465 Office dphe...@emerginghealthit.com www.emerginghealthit.com www.montefiore.org From: UXB Sent: Wednesday, August 27, 2014 1:46 PM To: cf-talk Subject: RE: Querying a comma separated list I assume from the example you are storing the a comma delimited list in the Field gtype and need to query all records that match one or more items from the stored list. Here are some ways to accomplish what you want to do plus some other tricks. http://www.sommarskog.se/arrays-in-sql-2005.html Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com -Original Message- From: te...@it-werks.com te...@it-werks.com [mailto:te...@it-werks.com] Sent: Tuesday, August 26, 2014 8:13 PM To: cf-talk Subject: Querying a comma separated list I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: checked>Solid checked>Open checked>Insulated checked>Combination At form submit I update the record: update pgallery set gtype = '#tt#' where id = #picid# Now here's where I screw up: select * from pgallery where listContains(gtype, "1") Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359204 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Querying a comma separated list
I assume from the example you are storing the a comma delimited list in the Field gtype and need to query all records that match one or more items from the stored list. Here are some ways to accomplish what you want to do plus some other tricks. http://www.sommarskog.se/arrays-in-sql-2005.html Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com -Original Message- From: te...@it-werks.com te...@it-werks.com [mailto:te...@it-werks.com] Sent: Tuesday, August 26, 2014 8:13 PM To: cf-talk Subject: Querying a comma separated list I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: checked>Solid checked>Open checked>Insulated checked>Combination At form submit I update the record: update pgallery set gtype = '#tt#' where id = #picid# Now here's where I screw up: select * from pgallery where listContains(gtype, "1") Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359203 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
Your query should be: select * from pgallery where gtype = 1 if you only want items with a gtype of 1 or select * from pgallery where gtype IN (1,2,3,4) if you want multiple types. You can also set a variable of such as gtypelist ="1,2,3,4) then do select * from pgallery where gtype IN (#gtypelist) On Tue, Aug 26, 2014 at 8:13 PM, te...@it-werks.com te...@it-werks.com wrote: > > I haven't done this is ages and could use some help, please. > Now here's where I screw up: > > select * from pgallery where listContains(gtype, "1") > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359201 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
Back when i used to pack fields with comma delimited strings, i used to do
something like this:
select * from pgallery where concat(',', gtype,',') like '%,1,%'
(add the , to the front and back of the string to test, so the value you
want is always ",VAL,")
this then only runs one like query, rather than multiple.
not sure if it is better, but it was simpler for me to understand
hth
Jerry Milo Johnson
On Tue, Aug 26, 2014 at 8:41 PM, Bill Moniz wrote:
>
> I'm pretty certain you can't do what you're trying to do directly. The
> comma delimited list you have stored in gtype is just a string as far as
> the DB engine is concerned and cannot be searched as a list. If you must
> store it this way, I have achieved the result I think you want, in the
> past, with the following somewhat ugly query:
>
>
> select*
> from pgallery
> wheregtype LIKE '1' OR gtype LIKE '1,%' OR gtype LIKE '%,1' OR gtype
> LIKE '%,1,%'
>
>
> Basically this manually searches the string for the four possible positions
> for your target element in a comma delimited list:
> 1. Element is the only thing in the list
> 2. Element begins the list
> 3. Element ends the list
> 4. Element is in the list between two other elements
>
> Hope that helps. Also I'm basing this on my experience with MS-SQL so
> apologies if MS-ACCESS actually does provide a way to do this natively.
>
>
> On 27 August 2014 10:32, Jon Clausen wrote:
>
> >
> > listContains() isnât a function of the database but, rather, a CFML
> > function. If youâre storing the list as a string list, and are querying
> > the database, then your query will have to use Access string functions as
> > it doesnât know CFML:
> >
> > http://www.techonthenet.com/access/functions/
> >
> > HTH,
> > Jon
> >
> >
> >
> > On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com <
> > te...@it-werks.com> wrote:
> >
> > >
> > > I haven't done this is ages and could use some help, please.
> > >
> > > Here I define a list of checkboxes of picture types:
> > >
> > > > '1'>checked>Solid
> > > > '2'>checked>Open
> > > > '3'>checked>Insulated
> > > > '4'>checked>Combination
> > >
> > > At form submit I update the record:
> > >
> > > update pgallery set gtype = '#tt#'
> > > where id = #picid#
> > >
> > >
> > > Now here's where I screw up:
> > >
> > > select * from pgallery where listContains(gtype, "1")
> > >
> > >
> > > Here;s the error:
> > > Error Executing Database Query.
> > > [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
> > Microsoft Access Driver] Undefined function 'listContains' in expression.
> > >
> > > Terry
> > >
> > >
> > >
> >
> >
>
>
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359198
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
I'm pretty certain you can't do what you're trying to do directly. The comma delimited list you have stored in gtype is just a string as far as the DB engine is concerned and cannot be searched as a list. If you must store it this way, I have achieved the result I think you want, in the past, with the following somewhat ugly query: select* from pgallery wheregtype LIKE '1' OR gtype LIKE '1,%' OR gtype LIKE '%,1' OR gtype LIKE '%,1,%' Basically this manually searches the string for the four possible positions for your target element in a comma delimited list: 1. Element is the only thing in the list 2. Element begins the list 3. Element ends the list 4. Element is in the list between two other elements Hope that helps. Also I'm basing this on my experience with MS-SQL so apologies if MS-ACCESS actually does provide a way to do this natively. On 27 August 2014 10:32, Jon Clausen wrote: > > listContains() isnât a function of the database but, rather, a CFML > function. If youâre storing the list as a string list, and are querying > the database, then your query will have to use Access string functions as > it doesnât know CFML: > > http://www.techonthenet.com/access/functions/ > > HTH, > Jon > > > > On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com < > te...@it-werks.com> wrote: > > > > > I haven't done this is ages and could use some help, please. > > > > Here I define a list of checkboxes of picture types: > > > > '1'>checked>Solid > > '2'>checked>Open > > '3'>checked>Insulated > > '4'>checked>Combination > > > > At form submit I update the record: > > > > update pgallery set gtype = '#tt#' > > where id = #picid# > > > > > > Now here's where I screw up: > > > > select * from pgallery where listContains(gtype, "1") > > > > > > Here;s the error: > > Error Executing Database Query. > > [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC > Microsoft Access Driver] Undefined function 'listContains' in expression. > > > > Terry > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359197 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Querying a comma separated list
listContains() isnt a function of the database but, rather, a CFML function. If youre storing the list as a string list, and are querying the database, then your query will have to use Access string functions as it doesnt know CFML: http://www.techonthenet.com/access/functions/ HTH, Jon On Aug 26, 2014, at 8:13 PM, te...@it-werks.com te...@it-werks.com wrote: > > I haven't done this is ages and could use some help, please. > > Here I define a list of checkboxes of picture types: > > '1'>checked>Solid > '2'>checked>Open > '3'>checked>Insulated > '4'>checked>Combination > > At form submit I update the record: > > update pgallery set gtype = '#tt#' > where id = #picid# > > > Now here's where I screw up: > > select * from pgallery where listContains(gtype, "1") > > > Here;s the error: > Error Executing Database Query. > [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft > Access Driver] Undefined function 'listContains' in expression. > > Terry > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Querying a comma separated list
I haven't done this is ages and could use some help, please. Here I define a list of checkboxes of picture types: checked>Solid checked>Open checked>Insulated checked>Combination At form submit I update the record: update pgallery set gtype = '#tt#' where id = #picid# Now here's where I screw up: select * from pgallery where listContains(gtype, "1") Here;s the error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'listContains' in expression. Terry ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359195 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
