问题描述
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]%'
这篇关于如何检查 VARCHAR 字符串是否为(非)十六进制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！