What is a database and Concatenation in SQL?

Concatenation or concat means to combine a string, text or data to gather and make one string in series without any middle variable”. Every language has its own operator for concatenation of multiple strings to gather. For example:

► Play

SQL provides a lot of ways to combine the string to gather. All methods are explaining below.

How to Concatenate in SQL?

We can use Build-in Functions and some special operator to concatenate the string in SQL.

► Play

Plus operator

In SQL “ + ” is very useful for concatenating two or more strings to gather. The syntax is given below,

SELECT String1 + ‘ ‘ + String2 AS label FROM Employees ;

Example:

SELECT ( LastName + ‘ , ‘ + FirstName ) AS Name 

FROM Person.Person 

ORDER BY LastName ASC , FirstName ASC ;

The CONCAT ( ) Function in SQL

CONCAT ( ) is built-in function for concatenating strings in SQL. We pass the two arguments in CONCAT ( ) function, the first one is string and second is number or string. The result will in the form on one string. Syntax of CONCAT ( ) function:

► Play

SELECT CONCAT ( ‘ Comments:  ‘ , 9 ) AS Result ;

Result:

Comments: 9

The CONCAT_WS ( ) Function in SQL

CONCAT_WS ( ) is also build-in function in SQL, it work like CONCAT ( ) function but in CONCAT_WS ( ) we passed three arguments. The first argument is the separator then string1 and string2.

► Play

SELECT CONCAT_WS ( ‘ :  ‘ , ‘ Comments ‘ ,  9 ) AS Result ;

Result:

Comments: 9

Example of Built-in Functions for Concatenating in SQL

The real use of CONCAT ( ) function in query is given below:

SELECT

                               customer_id ,

                               first_name ,

                               last_name ,

                               CONCAT ( first_name , ‘ ‘ , last_name ) full_name

FROM

                               sales.customers

ORDER BY

                               full_name ;

How to Concatenate in SQL Oracle

Oracle is a good Database management system, oracle also supports multiple ways to concatenate the strings. CONCAT ( ) is function is work similarly that already mentioned above. But also we can concatenate the string using “ || “ ( two pipes ) operator.

► Play

SELECT FirstName || ‘ ‘ || LastName AS FullName

FROM Employees

CONCAT ( ) Function:

SELECT CONCAT ( FirstName , ‘ ‘ , LastName ) AS FullName

FROM Employees

SQL Query Concatenation Example in C# Code

A real example of the concatenation of query in C# code:

using Microsoft.SqlServer.Server;

using System;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

[SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = false, MaxByteSize = 8000)]

[Serializable]

public class Concatstr : IBinarySerialize {

    private StringBuilder result; 

    public void Init(){

        this.result = new StringBuilder();

    }

    public void Accumulate(SqlChars value)

    {

        if (value.IsNull || this.result.Length + value.Value.Length + 1 > 8000)

            return;

        this.result.Append(value.Value).Append(‘,’);

    }

    public void Merge(Concatstr other)

    {

        if (this.result.Length + other.result.Length > 8000)

            return;

        this.result.Append((object)other.result);

    }

    public SqlChars Terminate()

    {

        string empty = string.Empty;

        if (this.result != null && this.result.Length > 0)

            empty = this.result.ToString(0, this.result.Length – 1);

        return new SqlChars((SqlString)empty);

    }

    public void Read(BinaryReader r)

    {

        this.result = new StringBuilder(r.ReadString());

    }

    public void Write(BinaryWriter w)

    {

        string str = this.result.ToString();

        if (str.Length >= 4001)

            str = str.Substring(0, 4001);

        w.Write(str);

    }

}

Send us a feedback0/500

Do you like this article?
Yes
No