問題描述
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 字符串是否為(非)十六進制?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！