GO
-- Creating Test Table
CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400), field3 VARCHAR(400),field4 VARCHAR(400))
GO
-- Inserting Data into Table
INSERT INTO testtable(field1,field2,field3,field4)
VALUES('24/Sep/2012','jim|frank|sue','Alan|Tom|George','Alice|Tony|Mike')

INSERT INTO testtable(field1,field2,field3,field4)
VALUES('25/Sep/2012','Tony|Steph|Al','Alice|Tony|Mike','Reg|Steve|John')

declare @SearchDate datetime = '20120924'

select item
	from TestTable
	cross apply dbo.DelimitedSplit8K(field2, '|') f2
	where field1 = @SearchDate

union all

select item
	from TestTable
	cross apply dbo.DelimitedSplit8K(field3, '|') f2
	where field1 = @SearchDate

union all

select item
	from TestTable
	cross apply dbo.DelimitedSplit8K(field4, '|') f2
	where field1 = @SearchDate

select item
from TestTable
cross apply dbo.DelimitedSplit8K(field2 + '|' + 
				field3 + '|' + 
				field4 + '|' + 
				field5 /*+ '|' + fieldN */, '|') f2