or just put this legwork on cf and that should be easier :) On Fri, Aug 5, 2011 at 10:55 AM, Chris Stoner <[email protected]> wrote: > > Another option is to: > > > 1. do a string replace to make the list into a list of xml values by > doing like: "<somexml><item>" + replace(value, ",","</item><item>") + > "</item></somexml>" > 2. Use the xpath functions build into sql to then query the nodes. > 3. Profit. > > Should work, and would give you more query options over the list. You could > create a view Around part 1, that just always has the xml field and query it > to your hearts content... > > > On Fri, Aug 5, 2011 at 10:09 AM, GMoney <[email protected]> wrote: > >> >> awesome, thanks jerry i'll take a look at this. The list is in random >> order. >> >> On Fri, Aug 5, 2011 at 9:06 AM, Jerry Barnes <[email protected]> wrote: >> >> > >> > It looks like the list has a random order. Is that the case or is it in >> > alphabetical order? Do you use user defined functions? >> > >> > Regardless, check this out: >> > http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows >> > >> > The key is the following: >> > >> > *SELECT Author, >> > NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + >> > Phrase + ',' , ID) - ID) , '') AS Word >> > FROM Tally, Quotes >> > WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , >> > ID - 1, 1) = ',' >> > AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0* >> > >> > >> > >> > From this template it looks like you could create a view with a column >> for >> > each value and then use a statement similar to this that groups by value >> > and >> > returns count. >> > >> > >> > >> > >> > J >> > >> > - >> > >> > Ninety percent of politicians give the other ten percent a bad >> reputation. >> > - >> > Henry Kissinger >> > >> > Politicians are people who, when they see light at the end of the tunnel, >> > go >> > out and buy some more tunnel. - John Quinton >> > >> > >> > >> >> > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:341287 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
