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.

Some time we run into bad database design were a single fields contain a comma delimited list of pointers or attributes; sometime we even do it ourselves to kick out some quick database… for example in a typical Coldfusion security framework you may have a database field with a comma delimited list of roles in the users table.

e.g.
uname: rob; roles: admin, dbadmin, user; datestamp: 6/13/2005

So, you may query the DB with a bunch of like operators which may or may not be accurate. You cannot query the DB with like ‘%admin%’ because it will return true if you’re an admin or dbadmin.

Of course you can use like ‘admin,%’ or ‘%,admin,%’ or ‘%,admin’ or ‘admin’… but that’s too much!

Here’s where my small SQL UDF’s come handy. I replicated the ListFind coldfusion function as a SQL function.

You can now query from the users table

select * from users where dbo.ListFind(roles,’admin’,’,’) > 0.

The third parameter works just like in CF so you can specify other characters such as pipe ‘|’ as a delimited if needed.

Download the SQL function here.