You are here

SQL :String Functions

String functions in SQL

String Functions

In this post we will discuss String functions in SQL. SQL support many functions for String manipulations like CONCATE(), LEFT(), RIGHT() etc.

In this entire post for examples we use below table.

Table: persons

p_id

firstname lastname city
1 Amisha Patel

Anand

2

Bina Patel Baroda
3 Tina Doyle

v.v.nagar

4

Riya Patel Anand
5 Nikunj Patel

NULL

Concatenation:

  • T-SQL support two ways for string concatenation.

Concatenation using ‘+’ operator:

  • For concatenation of string we use + operator. When any of the inputs is NULL, the + operator returns a NULL.
  • If you want to replace a NULL with an empty string use ISNULL or COALESCE functions.

Example:

select firstname+’ ‘ +lastname as Name,city from persons;

output:

Name

city

Amisha Patel

Anand

Bina Patel

Baroda

Tina Doyle

v.v.nagar
Riya Patel

Anand

Nikunj Patel

NULL

select firstname+’ ‘ +city as info,city from persons;
output:

Info

city

Amisha Anand

Anand

Bina Baroda

Baroda

Tina v.v.nagar

v.v.nagar

Riya Anand

Anand

NULL

NULL

select ISNULL(firstname+’ ‘ +city,’No city’) as info,city from persons;
output:

Info

city

Amisha Anand

Anand

Bina Baroda

Baroda

Tina v.v.nagar

v.v.nagar

Riya Anand

Anand

No city

NULL

Concatenation using CONCATE() function:

  • The CONCATE() function returns a string that is the result of concatenating two or more string values.
  • CONCATE() will not return NULL if any string is NULL.

Syntax:
CONCATE(str1, str2,…, strn)
Example:
select CONCAT(firstname,+’ ‘,+city)as info from persons;

output:

info

Amisha Anand

Bina Baroda

Tina v.v.nagar

Riya Anand

Nikunj

LEFT():

  • Return the left part of a character string with the specified number of characters.

Syntax:
LEFT(expression, number)
Example:
select LEFT(firstname,3) as leftfromfname from persons;
output:

leftfromfname

Ami

Bin

Tin

Riy

Nik

RIGHT():

  • Returns a right part of a character string with the specified number of characters.

Syntax:
RIGHT(expression, number)
Example:
select RIGHT(firstname,3) as rightfromfname from persons;
output:

rightfromfname

Sha

Ina

ina

Iya

Unj

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Microsoft SQL 70-762 Practice Test.

Microsoft MCSA 70-761 Practice Test:

Leave a Reply

Top