-- that what I can see as your table:
declare @tbl table 
(
Col1 VARCHAR(10), ExpDate1 DATETIME,
Col2 VARCHAR(10), ExpDate2 DATETIME,
Col3 VARCHAR(10), ExpDate3 DATETIME,
Col4 VARCHAR(10), ExpDate4 DATETIME,
Col5 VARCHAR(10), ExpDate5 DATETIME)

-- If you want to search for string in "CONCATENATED" value of above columns you can do the following:

;with cte
as
(
    select  *
           ,Col1+' '+CONVERT(varchar(30),ExpDate1, [whateverformatyouwant]) + ' '
           +Col2+' '+CONVERT(varchar(30),ExpDate2, [whateverformatyouwant]) + ' '
           +Col3+' '+CONVERT(varchar(30),ExpDate3, [whateverformatyouwant]) + ' '
           +Col4+' '+CONVERT(varchar(30),ExpDate4, [whateverformatyouwant]) + ' '
           +Col5+' '+CONVERT(varchar(30),ExpDate5, [whateverformatyouwant]) AS ConcatenatedCol
    from @tbl
)

select * from cte where ConcatenatedCol LIKE '%search word%'

;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) 
AS 
(SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL
SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL
SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL
SELECT 104, 'D', 'C', 'D', 'D','D' UNION ALL
SELECT 105,'AA','A','A','A','A'UNION ALL
SELECT 106,'B','C','A','AA','D')
, CT
AS
(
 SELECT DISTINCT Record_id, Newcol
 FROM   Mysample 
 CROSS APPLY  (SELECT Newcol FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol) ) n
 WHERE Newcol IS NOT NULL
)

SELECT Record_id, MAX(Newcol) AS Newcol
FROM CT
GROUP BY Record_id
HAVING COUNT(*) = 1
UNION 
SELECT Record_id, Newcol
FROM CT WHERE Newcol = 'AA'