SQL ListFind saves the day!

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.