Javatpoint Logo
Javatpoint Logo

CONCAT Function in SQL

This string function adds two or more characters or strings to form a single new string in the result. If you pass only one string in the function, then it shown an error in the output. So, CONCAT() function requires at least two strings.

Syntax of CONCAT String Function

Syntax1: This syntax uses the CONCAT function with two or more column names of the SQL table:

In the syntax, we have to specify the name of those columns whose values we want to concatenate.

Syntax2: This syntax uses the CONCAT function with two or more strings:

Syntax3: This syntax uses the CONCAT function with two or more characters:

Examples of CONCAT String function

Example 1: The following SELECT query adds two characters to form a new string:

Output:

'Hi'    

Example 2: The following SELECT query gives a new string by adding more than 2 characters:

Output:

Wishes
Congratulations

Example 3: The following SELECT query adds two strings:

Output:

Sentence
NEW DELHI IS THE CAPITAL OF INDIA

Example 4: The following SELECT query adds more than two strings:

Output:

Have a Nice Day   

Example 5: The following SELECT query adds those two string which contain symbols:

Output:

#@##9Ski [email protected]%^&   

Example 6: The following SELECT query use space between two strings:

Output:

JavaTpoint Website.  

Example 7: This example uses the CONCAT function with the table in Structured Query Language.

In this example, we have to create a new SQL table through which we will execute the Concat() function on columns. The syntax for creating the new table in the SQL database is as follows:

The following CREATE statement creates the Student_Marks table:

The below INSERT queries insert the records of college Faculties in the Student_Marks table:

The following SELECT statement displays the inserted records of the above Student_Marks table:


Student_Id Student_First_Name Student_Middle_Name Student_Last_Name Student_Class Student_City Student_State Student_Marks
4001 Aman Roy Sharma 4 Chandigarh Punjab 88
4002 Vishal Gurr Sharma 8 Murthal Haryana 95
4007 Raj singhania Gupta 6 Ghaziabad Uttar Pradesh 91
4004 Yash Chopra Singhania 9 Jaipur Rajasthan 85
4011 Vinay Sharma Roy 8 Chandigarh Punjab 94
4006 Manoj Singhania Gupta 5 Ghaziabad Uttar Pradesh 83
4010 Ram Raheem Gupta 9 Lucknow Uttar Pradesh 89

Query 1: The following SELECT query uses the CONCAT function with the Student_First_Name, Student_Middle_Name, and Student_Last_Name columns of the above Student_Marks table:

This SQL statement concatenates the values of the first name, middle name, and last name of each student.

Output:

Student_First_Name Student_Middle_Name Student_Last_Name Name
Aman Roy Sharma AmanRoySharma
Vishal Gurr Sharma VishalGurrSharma
Raj singhania Gupta RajsinghniaGupta
Yash Chopra Singhania YashChopraSinghania
Vinay Sharma Roy VinaysharmaRoy
Manoj Singhania Gupta ManojSinghaniaGupta
Ram Raheem Gupta RamRaheemGupta

Query 2: The following SELECT query uses the CONCAT function with the Student_City and Student_State column of those Student whose Student_Id is greater than 4002 in the above Student_Marks table:

This SQL statement adds the values of city and state with a comma.

Output:

Student_Id Address
4007 Ghaziabad, Uttar Pradesh
4004 Jaipur, Rajasthan
4011 Chandigarh, Punjab
4006 Ghaziabad, Uttar Pradesh
4010 Lucknow, Uttar Pradesh






Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA