SQL ListFind saves the day! Part2: ListGetAt

So say you did run into a comma delimited field, but this time with IDs. To make things worth, there is a second comma delimited field that contains data related to the first field in the same order.

E.g.
Field1 (ids): 1,3,5,8,10
Field2 (prices): 10,44,18,40,115

Your task: you need to first the price for ID number 5.

Ok, so now in you coldfusion code you could query the entire table, loop through the records, add an extra column, and user querysetcell to match all prices for the specific ID. Of course, the ID might be in different positions for every row.

If definitely defeats the definition of relational database, but I have seen this time after time and it is extremely handy to have the solution already written by the time you need it.

Solution: Let’s combine the UDF ListGetAt with the ListFind that I wrote in my previous blog.

The ListFind will return the position for the ID in every row while ListGetAt returns the token at the specified position. It works just like it would in Coldfusion, but you can apply it to an entire recordset.

For example, let’s find the price for ID number 5 in the previous example

Select dbo.ListGetAt(field2,dbo.ListFind(field1,5,’,’),’,’) as thisPrice from table

Download the SQL function here.