SQL Server CONCATENATE Operations with SQL Plus + and SQL CONCAT functions
SQL Server CONCATENATE Operations with SQL Plus and SQL CONCAT functions
Incorrect syntax near ‘ + ‘.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ‘;
‘. If we want to print more single quotes, we need to define in the string in the following format with two single quotes. Expected Output: ‘Let’s explore SQL Server with articles on SQLShack’ We can modify SQL query as follow. 1 SELECT '''Let''s' + ' explore SQL Server with articles on SQLShack'''; Note: You should not confuse a single quote with the double quotes. SQL Server treats double quotes as a character. In the following query, we used a double quote in a combination of a single quote. 1 SELECT '"''Let''s' + ' explore SQL Server with articles on SQLShack'''; We can do SQL Server Concatenate operation using the SQL Plus(+) operator; however, it becomes complex if you need to use multiple single quotes. It is difficult to debug code as well since you need to look at all the single quotes combinations as well.
The data types varchar and date are incompatible in the add operator. We need to use SQL CONVERT function as per the following query, and it returns output without error message. 123 Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on'Declare @date date='20190422'Select @Text+' '+Convert (varchar,(@date)) as Result We do not need to use SQL CONVERT function to convert data type in SQL CONCAT function. It automatically does the conversion based on the input data type. 123 Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on'Declare @date date='20190422'Select concat(@Text,@date) as Result In the following table, we can see that data type conversion for input and output data types in SQL CONCAT function. Input Data Type Output Data Type SQL CLR NVARCHAR(MAX) NVARCHAR(MAX) NVARCHAR(MAX) NVARCHAR(<=4000 characters) NVARCHAR(<=4000 characters) VARBINARY(MAX) NVARCHAR(MAX) All other data types VARCHAR(<=8000) *if any parameters data type is NVARCHAR, the output value will be NVARCHAR(MAX)
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolSQL Server CONCATENATE Operations with SQL Plus and SQL CONCAT functions
May 13, 2019 by Rajendra Gupta This article explores SQL Server Concatenate operations using the SQL Plus (+) operator and SQL CONCAT function.Introduction
We use various data types in SQL Server to define data in a particular column appropriately. We might have requirements to concatenate data from multiple columns into a string. For example, in an Employee table, we might have first, middle and last name of an employee in different columns. In the following screenshot, we have top 10 records from an employee table whose middle name is NOT NULL (I will explain the reason for it as we move on in this article) 12345 SELECT TOP (10) [FirstName], [MiddleName], [LastName]FROM [AdventureWorks2017].[Person].[Person]where Middlename IS NOT NULLSQL Plus Operator Overview
Usually, we use a SQL Plus (+) operator to perform SQL Server Concatenate operation with multiple fields together. We can specify space character as well in between these columns.Syntax of SQL Plus operator
string1 + string2 + …….stringnExamples of SQL Plus Operator
Look at the following query. In this query, we use SQL Plus(+) operator and space between the single quote as a separator between these fields. 123456 SELECT TOP 10 FirstName, MiddleName, LastName, FirstName + ' ' + MiddleName + ' ' + LastName AS FullNameFROM [AdventureWorks2017].[Person].[Person]WHERE Middlename IS NOT NULL; In the output of SQL Server Concatenate using SQL Plus (+) operator, we have concatenate data from these fields (firstname, MiddleName and LastName) as a new column FullName. We have a drawback in SQL Server Concatenate data with SQL Plus(+) operator. Look at the following example. 12345 SELECT TOP 10 FirstName, MiddleName, LastName, FirstName + ' ' + MiddleName + ' ' + LastName AS FullNameFROM [AdventureWorks2017].[Person].[Person] In this example, we can see that if we have any NULL value present in any fields, we get output of concatenate string as NULL with SQL Plus(+) operator. We can use SQL ISNULL function with + operator to replace NULL values with a space or any specific value. Execute the following query and we can still use SQL Plus(+) operator to concatenate string for us. 12345 SELECT TOP 10 FirstName, MiddleName, LastName, ISNULL(FirstName, '') + ' ' + ISNULL(MiddleName, '') + ' ' + ISNULL(LastName, '') AS FullNameFROM [AdventureWorks2017].[Person].[Person]; Let’s look at another example of SQL Server Concatenate values with string as well as numeric values. In the following query, we want to concatenate first, middle, full name along with the NationalID number. 12345678 SELECT p.[Title], p.[FirstName], p.[MiddleName], p.[LastName], p.FirstName + ' ' + p.MiddleName + ' ' + p.LastName + 'NationalIDNumber is :' + e.NationalIDNumber AS EmpDetailFROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]WHERE p.Middlename IS NOT NULL; Suppose we want the single quote as well in the SQL Server Concatenate. In SQL Server, once we combine strings using SQL Plus(+) operator, it concatenates values inside single quotes. In the following query, we can see we specified two single quotes to display a single quote in the output. 1 SELECT 'Let''s' + ' explore SQL Server with articles on SQLShack'; If there is any mismatch or incorrect use of the single quote, you get following error message. Msg 102, Level 15, State 1, Line 4Incorrect syntax near ‘ + ‘.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ‘;
‘. If we want to print more single quotes, we need to define in the string in the following format with two single quotes. Expected Output: ‘Let’s explore SQL Server with articles on SQLShack’ We can modify SQL query as follow. 1 SELECT '''Let''s' + ' explore SQL Server with articles on SQLShack'''; Note: You should not confuse a single quote with the double quotes. SQL Server treats double quotes as a character. In the following query, we used a double quote in a combination of a single quote. 1 SELECT '"''Let''s' + ' explore SQL Server with articles on SQLShack'''; We can do SQL Server Concatenate operation using the SQL Plus(+) operator; however, it becomes complex if you need to use multiple single quotes. It is difficult to debug code as well since you need to look at all the single quotes combinations as well.
SQL CONCAT FUNCTION
Starting from SQL Server 2012, we have a new function to concatenate strings in SQL Server.Syntax of SQL CONCAT function
CONCAT ( string1, string2….stringN) We require at least two values to concatenate together and specify in the SQL CONCAT function.Examples
Let’s explore SQL CONCAT with an example. The following query, concatenate string and gives output as a single string. We specified multiple single quotes between each word to print space in between each word. 1 SELECT CONCAT( 'My',' ', 'Name',' ', 'is',' ', 'Rajendra',' ', 'Gupta') AS introduction We can use system functions as well in concatenate sting using SQL CONCAT function. 1 SELECT CONCAT( 'SQL ISNULL function published on',' ',GETDATE()-2) AS SingleString We use Getdate() function to get a specified date in a concatenated string as well. In the previous section, we explored that if we want to concatenate string using + operator and any of string has a NULL value, and we get the output as NULL. We use SQL ISNULL function to replace NULL values in the string. We need to use SQL ISNULL with each column containing NULL values. If we have a large number of the column that may have NULL values, it becomes complex to write such code. Let’s review this again with SQL CONCAT function. We only need to specify the SQL CONCAT function at once and specify all string columns. We get the output as a concatenated string. 12345 SELECT TOP 10 FirstName, MiddleName, LastName, CONCAT(FirstName, ' ',MiddleName,' ',LastName) AS FullNameFROM [AdventureWorks2017].[Person].[Person]; Note: If all the string passed in SQL CONCAT function have a NULL value, we get the output of this function also NULL.SQL CONCAT and data type conversion
SQL CONCAT function implicitly converts arguments to string types before concatenation. We can use SQL CONVERT function as well without converting the appropriate data type. If we concatenate string using the plus( +) operator, we need to use SQL CONVERT function to convert data types. Lets’ look this using the following example. In the following query, we want to concatenate two strings. In this example, data type of first string is Text while another data type is a date. 123 Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on'Declare @date date='20190422'Select @Text +' '+ @date as Result Execute this query, and we get the following output. Msg 402, Level 16, State 1, Line 3The data types varchar and date are incompatible in the add operator. We need to use SQL CONVERT function as per the following query, and it returns output without error message. 123 Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on'Declare @date date='20190422'Select @Text+' '+Convert (varchar,(@date)) as Result We do not need to use SQL CONVERT function to convert data type in SQL CONCAT function. It automatically does the conversion based on the input data type. 123 Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on'Declare @date date='20190422'Select concat(@Text,@date) as Result In the following table, we can see that data type conversion for input and output data types in SQL CONCAT function. Input Data Type Output Data Type SQL CLR NVARCHAR(MAX) NVARCHAR(MAX) NVARCHAR(MAX) NVARCHAR(<=4000 characters) NVARCHAR(<=4000 characters) VARBINARY(MAX) NVARCHAR(MAX) All other data types VARCHAR(<=8000) *if any parameters data type is NVARCHAR, the output value will be NVARCHAR(MAX)
Conclusion
In this article, we explored useful SQL functions to concatenate multiple values together using the SQL Plus(+) operator and SQL CONCAT function. If you had comments or questions, feel free to leave them in the comments below. Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022