問題描述

TL;DR 選擇行時(shí)如何測(cè)試字符串是否為十六進(jìn)制?

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

如果我有一個(gè)帶有 GUID 的表，其中一些是 Base36 而不是十六進(jìn)制:

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

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

我想獲取所有具有非十六進(jìn)制 GUID 的行.

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

對(duì)于單例，我可以嘗試 CONVERT(VARBINARY(32),[GUID],2) 并查看它是否失敗，但我不能在查詢中做到這一點(diǎn).如果我可以查詢 WHERE isNaN(parseInt(GUID,16))，它將是故障安全的(同時(shí)必須應(yīng)用于所有行).

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

當(dāng)然我可以全文搜索 F 后面的字母(WHERE [GUID] LIKE '%g%' OR [GUID] LIKE '%h%' OR ...)，但這最后度假村的方法讓我問這個(gè)問題:

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:

如何僅查詢(非)十六進(jìn)制字段?

推薦答案

;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]%'

這篇關(guān)于如何檢查 VARCHAR 字符串是否為(非)十六進(jìn)制?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！