Is there SQL equivalent to ColdFusion 's ListGetAt function??? Please help!

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","~") <-- I hope this makes sense... TIA - Ché

I have done something similar, perhaps the long way... looping through a delimited list, keeping a counter, and obtaining the value.. I can send you the code if you'd like. Mike ----- Excess quoted text cut - see Original Post for more ----- <-- > > I hope this makes sense... > > TIA - Ché

Che Vilnonis wrote: ----- Excess quoted text cut - see Original Post for more ----- 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

hmmm...this could be the EASIEST way to do this. thanks Jochem, I'll check it out! Please help! Che Vilnonis wrote: ----- Excess quoted text cut - see Original Post for more ----- <-- 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

Che Vilnonis wrote: > hmmm...this could be the EASIEST way to do this. > thanks Jochem, I'll check it out! Actually, it gets easier: WHERE FinalShipTo LIKE '%|%|%|%|Amsterdam|%|%|%' I would expect MS SQL Server (just saw your message) to support regular expressions, in which case you could match "^([^\|]*\|){4}Amsterdam.*$" and you don't even need to know the number of entries in the list. Jochem