如何检查 VARCHAR 字符串是否为(非)十六进制?

2021-09-10 00:00:00 hex tsql sql-server base36 string-conversion

TL;DR 选择行时如何测试字符串是否为十六进制?

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

如果我有一个带有 GUID 的表，其中一些是 Base36 而不是十六进制:

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

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

我想获取所有具有非十六进制 GUID 的行.

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

对于单例，我可以尝试 CONVERT(VARBINARY(32),[GUID],2) 并查看它是否失败，但我不能在查询中做到这一点.如果我可以查询 WHERE isNaN(parseInt(GUID,16))，它将是故障安全的(同时必须应用于所有行).

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).

当然我可以全文搜索 F 后面的字母(WHERE [GUID] LIKE '%g%' OR [GUID] LIKE '%h%' OR ...)，但这最后度假村的方法让我问这个问题:

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:

如何仅查询(非)十六进制字段?

推荐答案

;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

所以

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

相关文章