For ERROR_NUMBER 515, how to get the column name from ERROR_MESSAGE

In many SQL error messages, SQL Server does not mention column name. But in messages such as the one shown below, the error message does include the column name.

Question: How can we extract the column name from the following error message?

When inserting data into a row in SQL Server table, you get following error number 515 if you are not using a NOT NULL column name in the insert statement:

Msg 515:

Cannot insert the value NULL into column 'LastName', table 'CustomerDB.dbo.CustomerTable'; column does not allow nulls. INSERT fails.

Goal: To log the above error in a log table as: LastName is required

the Log table has Primary Key column and an Error Description column. The requirement is to make the Error description brief, user friendly, and more focused for a non-technical person.

Solution

This needs to be parsed out of the error text.

This may or may not be localised to the language of the logged in user depending on platform (Azure SQL database doesn't bother with this)

You can review the versions of this message available with

SELECT text,
       l.name as language
FROM sys.messages m
JOIN sys.syslanguages l ON l.msglangid = m.language_id
WHERE m.message_id = 515

to see the message text around the place holders in each language.

In SQL Server 2025 this will probably be easier with Regex.

An example with a deliberately perversely named column is below

SET NOCOUNT ON;

IF @@LANGUAGE NOT IN ('us_english', 'British') AND @@VERSION NOT LIKE 'Microsoft SQL Azure%'
    THROW 50000, 'Parsing expects English Language message', 1;


DECLARE @Tbl TABLE
(
[Cannot insert the value NULL into column 'foo', table 'bar'; 
column does not allow nulls. INSERT fails.] INT NOT NULL
)

BEGIN TRY
INSERT @Tbl VALUES (NULL)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 515
    THROW;

PRINT REGEXP_REPLACE(ERROR_MESSAGE(), 
       '\ACannot insert the value NULL into column ''(.+)'', table ''(.+)''; column does not allow nulls\. (.+) fails\.\z', 
       '\1',
        1,
        1,
        's'
       ) 
END CATCH

In Azure SQL database this outputs the expected column name element

Cannot insert the value NULL into column 'foo', table 'bar';
column does not allow nulls. INSERT fails.

For SQL Server 2022 you will need to consider whether you care about the possibility of column names with single quotes or other elements of the message or not.

Maybe the suggestion already given (by @Jonathan Willcock) in the comments of

SUBSTRING(ERROR_MESSAGE(), 43, CHARINDEX('''', ERROR_MESSAGE(), 43) - 43)

would be "good enough"