SQL Server String CONCAT() Function

SQL Server CONCAT() Function

The SQL Server CONCAT() function is used to concatenate two or more strings into a single string. This function is useful for combining multiple columns or string literals into a single string for display or further processing.

Syntax

SELECT CONCAT(string1, string2, ...);

The CONCAT() function takes two or more string arguments:

  • string1, string2, ...: The strings to be concatenated.

Example SQL Server CONCAT() Function Queries

Let's look at some examples of SQL Server CONCAT() function queries:

1. Basic CONCAT() Example

SELECT CONCAT('Hello', ' ', 'World') AS result;

This query concatenates the strings 'Hello', ' ', and 'World' into a single string. The result will be:

result
-------
Hello World

2. CONCAT() with Columns

SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This query concatenates the first_name and last_name columns with a space in between for each employee. The result will show the employee_id and full_name.

3. CONCAT() with Literals and Columns

SELECT CONCAT('Employee: ', first_name, ' ', last_name) AS employee_description
FROM employees;

This query concatenates the literal 'Employee: ' with the first_name and last_name columns for each employee.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CONCAT() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

In this example, we create a table named example_table with columns for id, first_name, and last_name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, first_name, last_name) VALUES (1, 'John', 'Doe');
INSERT INTO example_table (id, first_name, last_name) VALUES (2, 'Jane', 'Smith');
INSERT INTO example_table (id, first_name, last_name) VALUES (3, 'Alice', 'Johnson');

Here, we insert data into the example_table.

Step 3: Using the CONCAT() Function

This step involves using the CONCAT() function to concatenate the first_name and last_name columns.

SELECT id, CONCAT(first_name, ' ', last_name) AS full_name
FROM example_table;

This query retrieves the id, first_name, and last_name, and concatenates the first_name and last_name columns with a space in between for each row in the example_table. The result will be:

id  full_name
--- ----------------
1   John Doe
2   Jane Smith
3   Alice Johnson

Conclusion

The SQL Server CONCAT() function is a powerful tool for concatenating strings. Understanding how to use the CONCAT() function and its syntax is essential for effective string manipulation and data presentation in SQL Server.