SQL Searching Of Delimited List

Yet another SQL quickie and Coldfusion related too:

So you have a table in your database that stores a list of things, say, a related list of keys to another table. And you want to know if a given key exists in any entry in your table. And you definitely want to do this in SQL as grabbing the entire set of keys and individually iterating over them in Coldfusion (or PHP or whatever) would be wasteful. The solution, while not mine, is pretty straightforward, although the original version that I saw made it a wee confusing.

These solutions assume a comma delimited list. Replace the commas with your alternative delimiter if needed.

Original Version:

SELECT *
FROM myTable
WHERE ','+ myColumn + ',' LIKE '%,' +'targetValue'+ ',%'

For whatever reason, that just didn't sink in to me immediately. However, in retrospect, and after a little Coldfusion related simplification, it became pretty obvious:

<cfquery datasource="myDatasource" name="myQuery">
SELECT *
FROM myTable
WHERE ','+ myColumn + ',' LIKE '%,#targetValue#,%'
</cfquery>

So what's happening is that you are taking your list of, for example, 'value1,value2,valueN' and you are capping it with the delimiter so that you have the new list ',value1,value2,valueN,'. Then you are looking for the substring ',targetValue,'.

Now you could just do something like the following:

<!--- BAD! --->
<cfquery datasource="myDatasource" name="myQuery">
SELECT *
FROM myTable
WHERE myColumn LIKE '%#targetValue#%'
</cfquery>

The problem with this guy is that if you have two rows with key values '1001,1002' and '11001,11002' then a search for '1001' will give a false positive in the second one. The values in the list need to be explicitely delimited at the beginning and the end. You could delimit your list like this, '[10001][1002]' and that would be ok, but it'd probably be more of pain to work with.

Of course, with a better normalized database, you wouldn't run into this problem in the first place - or so I've heard.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1.001. Contact Blog Owner