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

Question

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?

That statement isn't valid. To start with you are missing the table in your INSERT clause, you have a trailing comma (,) after your 3rd column expression (and there is at least one more issue) — Thom A
– Thom A ♦, Commented Apr 29, 2022 at 16:00
Also, rather than T.Substring(... try substring(T.FullName ... — John Cappelletti
– John Cappelletti, Commented Apr 29, 2022 at 16:01
As for the problem, functions are used like you have in the SELECT; they aren't used like methods in C#. I'm not sure why you have mixed up both syntax formats, when you've initially used SUBSTRING correctly. — Thom A
– Thom A ♦, Commented Apr 29, 2022 at 16:01

Rachel Gallen · Accepted Answer · 2022-04-29 21:14:13Z

1

You have an extra comma at the end of your select statement:

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

Remove this comma and it should work fine.

edited Apr 29, 2022 at 21:14

Rachel Gallen

28.7k22 gold badges77 silver badges87 bronze badges

answered Apr 29, 2022 at 16:27

Codeman_119

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Lange Over a year ago

That is part of the problem but definitely not all of the issues. The insert statement doesn't have a table it is inserting into, just a list of columns.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related