I had an issue trying to match a field with hidden characters. I needed to see what non-printable
chars were present.
This script helped me out.
Declare @word nvarchar(max)
Select @word = 'Hello This is the test'
SELECT
@word = REPLACE(REPLACE(cdoLog.Remark,CHAR(13),''),CHAR(10),'')
FROM dbo.ContractDeliveryOrderRemarksLog cdoLog
WHERE
cdoLog.RemarkTypeID IN (2,4)
AND
cdolog.Remark LIKE '%Expired%'
AND
cdoLog.ContractID IN (5095,5083)
AND
ID=94252
--ID=93825
ORDER BY cdoLog.ContractID;
;with cte (Number)as
(Select 1
union all
select Number +1 From cte where number <len(@word)
)
select Number,Letter,ASCII(Letter) from Cte Cross apply (Select SUBSTRING(@word,number,1 ) ) as J(Letter)
