SQL Server CONCATENATE Operations with SQL Plus + and SQL CONCAT functions

SQL Server CONCATENATE Operations with SQL Plus + and SQL CONCAT functions

SQL Server CONCATENATE Operations with SQL Plus and SQL CONCAT functions

SQLShack

SQL Server training Español

SQL 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 NULL

SQL 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 + …….stringn

Examples 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 4
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.

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

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

Related posts

An overview of the CONCAT function in SQL with examples SQL string functions for Data Munging (Wrangling) Top SQL Server Books How to import/export JSON data using SQL Server 2016 FOR XML PATH clause in SQL Server 197,936 Views

Follow us

Popular

SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices

Trending

SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples

Solutions

Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server

Categories and tips

►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ▼Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ▼Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy
Share:
0 comments

Comments (0)

Leave a Comment

Minimum 10 characters required

* All fields are required. Comments are moderated before appearing.

No comments yet. Be the first to comment!