如何将子字符串语句设置为SQL Server中的有效列名称

Question

我有以下代码：

INSERT INTO [TranslateValidate]..[Policy] ([BirthDate],[FirstName],[LastName])
  SELECT
    [Table2309].[DOB],
    SUBSTRING(Full_Name, CHARINDEX(',', Full_Name) + 2, LEN(Full_Name)),
    SUBSTRING(Full_Name, 0, CHARINDEX(',', Full_Name))  
  FROM [Table2309] AS [Table2309]
  WHERE [Table2309].[clientid] = (SELECT
    MIN(clientid)
  FROM Table2309 T
  WHERE T.Date_of_Birth = Table2309.Date_of_Birth
  AND T.Substring(Full_Name, CHARINDEX(',', Full_Name) + 2, LEN(Full_Name)) = Table2309.Substring(Full_Name, CHARINDEX(',', Full_Name) + 2, LEN(Full_Name))
  AND T.Substring(Full_Name, 0, CHARINDEX(',', Full_Name)) = Table2309.Substring(Full_Name, 0, CHARINDEX(',', Full_Name))

这将有一个错误消息

无法找到“ C”列或用户定义的函数或汇总的“ C.substring”，或该名称模棱两可。

如果我添加[]用于子弦部分，则应该错误消息

无效列名称'substring（full_name，...

如何解决此问题？

Answer 1

您在选定语句的结尾有一个额外的逗号：

SELECT
    [Table2309].[DOB],
    SUBSTRING(Full_Name, CHARINDEX(',', Full_Name) + 2, LEN(Full_Name)),
    SUBSTRING(Full_Name, 0, CHARINDEX(',', Full_Name))**,**

删除此逗号，它应该可以正常工作。