Comma Separated columns - Search

Report · Dec 23, 2009

Hi fellow,

I need to query my db with a comma separated list but the results are not correct...What is the reason?

My typical db problem_code column has valuas as :

Problem_code

--------------------

1

2,3

5,6,7,8

5

----------

And the list values I am sending as ("1","3")...etc

SELECT * from table

where

problem_code in ('2','1')

Thank you in advance..

Report · Dec 23, 2009

The biggest problem is a poorly designed database. If possible, normalize it. If you don't know how, I've heard good things about the book, Database Design for Mere Mortals.

Report · Dec 23, 2009

Sorry it is not my design... what is the solution?

Report · Dec 24, 2009

If you're sking SQL questions, you'd better off asking on a SQL forum. But here you are, so let's have a look at it.

Firstly, the other thing to always mention when raising an SQL question is to articulate what DB system you're using. Because all of them handle SQL differently, and have their own extensions.

In fact reading this is probably a good idea: http://www.catb.org/~esr/faqs/smart-questions.html

Problem_code
--------------------
1
2,3
5,6,7,8
5
----------
And the list values I am sending as ("1","3")...etc
SELECT * from table
where
problem_code in ('2','1')
problem_code in ('2','1')
Thank you in advance..

What are you seeing here that's not expected (I presume the double-up of problem_code in ('2','1') is a typo)?

Are you wanting to get any rows from TABLE which have either 1 or 2 in them? That's not what you're asking for in your WHERE statement though, is it?

Really, you should be normalising your data, because whilst you can write contorted logic to do what you need here, it will not perform well and certainly will not scale. So it for be somewhat poor practice to perpetuate the current situation if it's at all possible not to. The best thing to do is to normalise your data and refactor your code. Which will probably mean going to your boss and say "this DB schema is f*cked, and needs reworking. Needs reworking".

Depending on your DB system, you're probably going to need to loop over the list you want to filter on and do a "WHERE problem_code LIKE" on each element of the list. I think Oracle can do the sort of query you want to do - match elements of one list from another list - but I'm not sure.

--

Adam

Report · Dec 24, 2009

Yes we are on oracle 9g... Yes boss will use "F" word for himself since I already they told it is not normalized....

What are you seeing here that's not expected (I presume the double-up of problem_code in ('2','1') is a typo)?

>>>sure

Are you wanting to get any rows from TABLE which have either 1 or 2 in them? That's not what you're asking for in your WHERE statement though, is it?

>>>Yes i need to get the rows as well.

Report · Dec 24, 2009

This is putting a bandaid on a decaptitation but, here you go

select fields

from tables

where 1 = 2

or TheField like '#ThisNumber#,%'

or TheField like '%,#ThisNumber#,%'

or TheField like '%,#ThisNumber#'

or TheField = '#ThisNumber#'

</cfloop>

Don't expect it to run fast.

By the way, I have vague recollections of reading something on this forum that it is mySql that has the functionality to compare lists.