Find rows with specific string inside column text

I’m new in working with SQL Server and I’m trying to select all rows which has a specific string inside a column.
The field has a big string of characters, for example:

0x8F28EA0000000000000094EFFF00275F0A7F0000F86FFF00275EF57F0000

I tried using the simple

SELECT * FROM DB WHERE COLUMN LIKE '%0F86FFF00%' 

But as I have read that SQL Server doesn’t have this syntax so how could I do it differently but get the same result?

Your column is most likely of the VARBINARY data type, so it’s visual representation starts with 0x and displays hex characters (from 0 to F). You can’t simply use LIKE against a VARBINARY column, since LIKE operates with string data types like VARCHAR or NVARCHAR.

You can convert your VARBINARY to the string you are seeing by using CONVERT with the 3rd parameter as 1, check this example:

DECLARE @Varbinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), 'This is my string value.')

SELECT
    OriginalRepresentation = @Varbinary,
    ConvertToString = CONVERT(VARCHAR(MAX), @Varbinary),
    ConvertToStringHex = CONVERT(VARCHAR(MAX), @Varbinary, 1)

Result:

OriginalRepresentation                              ConvertToString             ConvertToStringHex
0x54686973206973206D7920737472696E672076616C75652E  This is my string value.    0x54686973206973206D7920737472696E672076616C75652E

If you are using SSMS, when you query a VARBINARY column, SSMS is automatically converting your VARBINARY to the hex-string representation so it’s “interpretable” to the user.

So to correctly filter using LIKE:

SELECT * FROM DB WHERE CONVERT(VARCHAR(MAX), [COLUMN], 1) LIKE '%0F86FFF00%'