CODEWITHSUNDEEP
sqlsql-server
Pryach
Pryach

Reputation: 417

SELECT with comma separation in column

--Create/Populate [#Filters]:
if object_id('tempdb..[#Filters]','U') is not null drop table [#Filters]
go
create table [#Filters]
(
 [filterid] int
 ,[filtertype] varchar(50)
 ,[filtername] varchar(50)
 ,[filtercriteria] nvcarchar(max)
)
go
set nocount on
insert [#Filters] select 1, 'Worklist', 'WL1', 'ID_SlotAssignmentFolder=1'
insert [#Filters] select 2, 'Worklist', 'WL2', 'ID_SlotAssignmentFolder=2'
insert [#Filters] select 3, 'Worklist', 'WL3', 'ID_SlotAssignmentFolder=3'
insert [#Filters] select 4, 'Filter', 'Filter1', 'filter_after_hours_offset=0,time_of_day_begin=00:00,worklistKey=1'
insert [#Filters] select 5, 'Filter', 'Filter2', 'filter_after_hours_offset=0,time_of_day_begin=00:00,worklistKey=1,worklistKey=2'
insert [#Filters] select 6, 'Filter', 'Filter3', 'filter_after_hours_offset=0,time_of_day_begin=00:00,worklistKey=2'
go
--select * from [#Filters]

I did not build this table, I am just trying to query from it.

I have a table called Filters. It contains two types, Filters and Worklists. Filters reference Worklists in the 'filtercriteria' column.

In the above example, Filter1 references WL1, Filter2 references WL1 and WL2, and Filter3 references WL2.

I need a query that will search the filters to ensure all of the Worklists are being referenced. For example, I want to be able to search the above table and for it to return WL3 because WL3 is not referenced in any of the filters.

There is other information in the filtercriteria column, so I'm not sure how to strip out everything except for the worklistKey info, and with that returned, just search for Worklists that aren't referenced in any Filter.

Upvotes: 0

Views: 77

Answers (1)

Hart CO
Hart CO

Reputation: 34774

In SQL Server you can use XML functionality to parse a delimited field into multiple rows:

SELECT DISTINCT REPLACE(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))),',','') AS filters
FROM
    (SELECT filterid,CAST('<XMLRoot><RowData>' + REPLACE(filtercriteria,'worklistKey=','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
     FROM   #Filters
     WHERE filtertype = 'Filter'
    )t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
WHERE ISNUMERIC(REPLACE(LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))),',','')) = 1

In this case we use 'worklistKey=' as the delimiter to parse on, and use an ISNUMERIC() check to filter out the non-numbers (rows that don't have a worklistKey).

This gets you the distinct list of Worklists referenced by Filters, you'll have to left-join from a list of all filters to finish this out. Might get messy if the reality is much different from your sample.

Upvotes: 1

Related Questions