2022-04-30

How to set substring statement as valid column name in SQL Server

I have a code like the following:

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

This would have an error message

Cannot find either column "c" or the user-defined function or aggregate "c.Substring", or the name is ambiguous.

If I add [] for Substring part, this would should error message

Invalid column name 'Substring(Full_Name,...

How do I resolve this problem?



No comments:

Post a Comment