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:
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
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)
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
SELECT * FROM DB WHERE CONVERT(VARCHAR(MAX), [COLUMN], 1) LIKE '%0F86FFF00%'