Comparing three Comma separated values in SQL

问题:

Comparing two comma separated values in sql, we can get done by using Split kind of functions.(Three columns also we can do !) But, without using the functions is that possible?

Let me give an example.

The query should check the following conditions

(1) Select the tasks from Table- 1 That is Planned. But Not in done or declined columns.

(2) Return only the mandatory tasks

Using Split functionality(UD Functions) or Cursor is making query more complex and it swallows the time as well.

Is there any alternative solution to get this done? Possible with single query ?

Thanks in advance.

回答1:

No there is no magic bullet. If you really want to increase performance this could be normalized (maybe indexed). Also, more standard queries would work without a split function.

Depending on the exact situation it might be different but from what you show I would have a tasks table with one row for every task that related to table1 which appears to be 'jobs' or 'projects' that contain many tasks.

Just being in the table means that they are planned tasks with columns for mandatory, declined and completed.

If you can not normalize this data then you are likely stuck with split functions. There are articles about getting great performance from split functions with CLR functions appearing to run quite fast.