Che Vilnonis wrote:
> I have a column in a db that stores data with a pipe [|] delimiter.
> I would like to write a SQL query that filters data based on a segment
> of data within a pipe delimited variable.
>
> Basically, is there SQL code that is equivalent to the psuedo CF code
> below? A SQL version of ListGetAt if you will???
>
> --> WHERE FinalShipTo = ListGetAt("#getOrderInfo.FinalShipTo#","5","~") <--
You didn't tell which database you are using, so I will just give the
SQL:1999 syntax for it.
In SQL you can only do this if you know the total number of entries in
the list beforehand. So suppose you want to match the 5th out of 8
entries to the value 'Amsterdam', the SQL:1999 syntax for it would be:
WHERE FinalShipTo SIMILAR TO '%\|%\|%\|%\|Amsterdam|%\|%\|%'
For custom extensions, check the string function chapter of your DBMS
manual.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
