How to check if VARCHAR strings are (not) hexadecimal?

Question

TL;DR   How to test if string is hex when selecting rows?

---

If I had a table with GUIDs where some of them are Base36 instead if hexadecimal:

  ID | GUID
  ===|=================================
   1 | CAFEBABECAFED00DB16B00B515BADA55
   2 | 0123456789ABCDEFGHIJKLMNOPQRSTUV
   3 | DEADBEAF4D15EA5EDEFEC8EDFEE1DEAD

I want to get all rows with GUIDs that are not exclusively hexadecimal.

For a singleton, I could try CONVERT(VARBINARY(32),[GUID],2) and see if it fails, but I can’t to that in a query. If I could query for WHERE isNaN(parseInt(GUID,16)), it would be fail-safe (while having to be applied to all rows).

Of course I could full-text search for letters following F (WHERE [GUID] LIKE '%g%' OR [GUID] LIKE '%h%' OR …), but this last resort kind of approach led me to asking this question:

How to query for (non-)hex fields only?

Answer 1

Just use LIKE with a wildcard character for a specific range:

SELECT * FROM [GUIDs] WHERE [GUID] LIKE '%[g-z]%';

Answer 2

;WITH rw AS
(
    SELECT '0000AF0012B' ishexornot
    UNION ALL
    SELECT '0000AF0012G'
    UNION ALL
    SELECT '0000AF0012 '
)
SELECT *, CASE WHEN ishexornot LIKE '%[^0-9A-F]%' THEN 0 ELSE 1 END
FROM rw

so

WHERE [GUID] LIKE '%[^0-9A-F]%'

Answer 3

Starting from SQL Server 2012, you can employ TRY_CONVERT():

declare @t table (
    Id int identity (1,1) primary key,
    Value varchar(100) not null
);

insert into @t (Value)
values
('F6AA1EE0-DF55-43E8-92ED-B2A84E621485'),
('F32E4621CE004C47889DEBDA9BA790AF'),
('09F94C95-9004-4C7C-8F5D-9CA374546288'),
('5DAFD4C7-C780-4F32-9BF1-52027371546A'),
('4D81BD419BFE42EA956E715023EF3B77');

select t.*
from @t t
where try_convert(uniqueidentifier, t.Value, 2) is null;

Unlike with CONVERT(), the query in the sample works without errors, and returns rows 2 and 5.

Answer 4

In postgres you can use POSIX regular expressions

select "GUID" ~ '[0-9A-F]{32}' as is_hex from the_table;

The above checks if the length of the string is 32 as well