如何将子字符串语句设置为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.子字符串"，或者名称有歧义。

如果我为Substring部分添加[]，这将会出现错误消息

无效的列名'Substring(Full_Name，…)

如何解决这个问题?

Answer 1

在select语句的末尾有一个额外的逗号:

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

去掉逗号，应该就可以了。