SQL Server 2008 将 VARCHAR(50) 转换为 Uniqueidentifier

Question

我正在尝试将一个varchar(50)列转换为uniqueidentifier，但是一直出现以下错误，我不知道原因：

"Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier."

该列中的数据目前是有效的唯一标识符。

我想知道正确的做法是什么？

谢谢。

- Alexander Nyquist

---

你想要转换的字符串是什么？ - gbn

Answer 1

你是否有包含空字符串的列？即非 NULL，但字符串长度为 0。

或者你是否有包含非标准字符的 GUID？即不是 0-9、A-F 的字符？

在我接手这个应用程序之前，我们有一些非标准的 GUID。

编辑：

对于未来的帮助，此脚本可帮助您查找任何无效的行：

SELECT    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0'), COUNT(*)
FROM TABLE
GROUP BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0')

任何包含无效Guid的行都会显示出来，可以通过以下方式找到：

SELECT    *, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0')
FROM TABLE
WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GUID, '1', '0'), '2', '0'), '3', '0'), '4', '0'), '5', '0'), '6', '0'), '7', '0'), '8', '0'), '9', '0'), 'A', '0'), 'B', '0'), 'C', '0'), 'D', '0'), 'E', '0'), 'F', '0') != '00000000-0000-0000-0000-0000000000'

- ck

Answer 2

在5000行中，我有一行包含了一个非十六进制有效字符。

- Alexander Nyquist

Answer 3

我知道这个问题已经有答案了，但是还有一种更加优雅的方法。您可以使用简单的仅一个字符通配符语法（即[]）在LIKE子句中允许测试有效的十六进制数字（即0-9和A-F）。通过使用带有单字符搜索的LIKE子句，您还可以以比REPLACE方法更易读的方式强制执行有效GUID / UNIQUEIDENTIFIER的格式，因为该方法必须先将所有十六进制数字归一化为0，然后再与有效格式进行比较。

设置

SET NOCOUNT ON;
IF (OBJECT_ID('tempdb.dbo.#GUIDs') IS NOT NULL)
BEGIN
  DROP TABLE #GUIDs;
END;

CREATE TABLE #GUIDs (ID INT NOT NULL, TheGUID VARCHAR(50) NULL);

INSERT INTO #GUIDs (ID, TheGUID)
  SELECT tmp.ID, tmp.TheGUID
  FROM  (
      SELECT  1, 'E1A21B62-ACC4-4ACB-B284-0F0233F19EDA' -- valid
      UNION ALL
      SELECT  2, '50178543-11E6-40D2-87F1-9C4676DCF542' -- valid
      UNION ALL
      SELECT  3, '' -- invalid: empty string
      UNION ALL
      SELECT  4, '4EB30267-0EB4-413A-9B05-6EDDB943C7D8' -- valid
      UNION ALL
      SELECT  5, '4EB30267-0EB4-413A-9Z05-6EDDB943C7D8' -- invalid: has a "Z"
      UNION ALL
      SELECT  6, NULL -- invalid: is NULL
      UNION ALL
      SELECT  7, '18EAE6C5-7256-4598-AA0A-837718145001' -- valid
      UNION ALL
      SELECT  8, '18eae6c5-7256-4598-aa0a-837718145001' -- valid (lowercase version of #7)
      UNION ALL
      SELECT  9, '18EAE6C5-7²56-4598-AA0A-837718145001' -- invalid: has superscript "2"
        ) tmp (ID, TheGUID);

测试

下面的示例显示了在GUID的每个十六进制数字位置上使用32组[0-9A-F]，并在适当的位置添加破折号（-）。请注意：

• LIKE模式中每行末尾的反斜杠（\）都是T-SQL Line Continuation character，
• 您应该使用二进制排序规则，以确保“0-9”和“A-F”的范围不匹配任何具有该范围内但不是具体十进制数字的值的字符。例如，上标“2”字符（²）不是十进制2，但在使用范围通配符时具有值2，并且使用Unicode比较规则（这是用于所有NVARCHAR数据的规则，甚至适用于VARCHAR如果排序规则是Windows排序规则——排序名称不以SQL_开头）。测试行＃9验证了这种情况。但是，执行二进制比较需要使范围模式[0-9A-Fa-f]或将列包装在UPPER()函数中。除非您使用SQL Server 2000或2005，否则请使用Latin1_General_100_BIN2，否则请使用Latin1_General_BIN。

SELECT  ID, TheGUID, CONVERT(UNIQUEIDENTIFIER, TheGUID) AS [Converted]
FROM    #GUIDs
WHERE   UPPER(TheGUID) COLLATE Latin1_General_100_BIN2 LIKE
'[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]\
[0-9A-F]';


SELECT  ID, TheGUID AS [BAD]
FROM    #GUIDs
WHERE   UPPER(TheGUID) COLLATE Latin1_General_100_BIN2 NOT LIKE
'[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-\
[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]\
[0-9A-F]'
OR      TheGUID IS NULL;

虽然这个问题是在 SQL Server 2008 的背景下提出的，但对于使用 SQL Server 2012 或更新版本的任何人来说，TRY_CONVERT 函数使这更加容易：

SELECT  tmp.ID, tmp.TheGUID AS [BAD]
FROM    #GUIDs tmp
WHERE   TRY_CONVERT(UNIQUEIDENTIFIER, tmp.[TheGUID]) IS NULL;
/*
ID    BAD
3     
5     4EB30267-0EB4-413A-9Z05-6EDDB943C7D8
6     (NULL)
9     18EAE6C5-7²56-4598-AA0A-837718145001
*/

- Solomon Rutzky