Commonly Used Built In String Functions In SQL Server

Introduction

In this article, you will learn about the commonly used string functions in SQL Server.

• LTRIM ()
• RTRIM ()
• LOWER ()
• UPPER ()
• REVERSE ()
• LEN ()
• LEFT ()
• RIGHT ()
• CHARINDEX ()
• SUBSTRING ()
• REPLACE ()
• STUFF ()

Explanation

LTRIM(string expression)

It is used to remove blank space on the left hand side of the given string.

For example,

RTRIM(string expression)

It is used to remove blank space on the Right hand side of the given string

For example,

Let’s check these functions how to use full in real time. To run, use script given below in SQL Server. 

CREATE DATABASE db_Test
GO
use db_Test
GO
CREATE TABLE Employee(
    [EMPID][nvarchar](30) NOT NULL, [FName][nvarchar](150) NULL, [LName][nvarchar](50) NULL, [Salary][money] NULL)
GO
INSERT[dbo].[Employee]([EMPID], [FName], [LName], [Salary]) VALUES('EMP101', 'Vishal ', 'c k', 15000.0000)
INSERT[dbo].[Employee]([EMPID], [FName], [LName], [Salary]) VALUES('EMP102', ' Sam', ' n ', 20000.0000)
INSERT[dbo].[Employee]([EMPID], [FName], [LName], [Salary]) VALUES('EMP106', 'Mahesh', ' d', 18000.0000)
INSERT[dbo].[Employee]([EMPID], [FName], [LName], [Salary]) VALUES('EMP105', 'Ravi', 'kumar', 20000.0000)
GO

As you can see the script given above, I have given some spaces for first name and last name. To check the functions given above, use following.

select FName+' '+ LName from Employee

In this select statement, I have given only one space between FName and Lname and the output will be, as shown below

In this output, you can see in the first row record, first name needs to be inserted, which is Vishal, followed by 3 Spaces, which is because I have inserted FName as Vishal and two spaces and in the second row, sam is coming after four spaces. I wanted the result  to be first name without space and one space and second name. In this case, we can use LTRIM and RTRIM to trim and remove both the side of spaces, select statement needs to be given, as shown below.

select RTRIM(LTRIM(FName))+' '+ RTRIM(LTRIM(LName)) from Employee

The output is given below.

Now, you can see the clean second result after applying LTRIM and RTRIM.

LOWER(string expression) 

It is used to convert all the characters in the given string expression to lower case letters.

UPPER(string expression) 

It is used to convert all the charecters in the given string expression to upper case letters.

Example,

REVERSE(string expression) 

It is used to reverse all the characters in the given string expression.

Example,

LEN(string expression) 

it is used to get the count of the total characters in the given string expression, excluding the blanks at the end of the expression and including the blanks at the beginning of the expression..

Example,

LEFT (character expression, integer expression) 

It is used to return the specified number of characters from the left hand side of the given character expression.

For example,

RIGHT (character expression, integer expression) 

It is used to return the specified number of characters from the right hand side of the given character expression.

For example,

CHARINDEX (character expression to find, string input expression to search, [start index]) 

It is used to return the starting position of the specified character expression in a string input. Here, start index is optional.

For example, 

select CHARINDEX('@', '[email protected]')
select CHARINDEX('@', 'rafnas@[email protected]', 8)

The output is given below.

SUBSTRING (character expression, start index, length) 

It is used to return part of the string from the given expression.

For example, from an email address to get only domain name, use the script, as shown below. 

DECLARE @input nvarchar(50)
set @input = '[email protected]'
select SUBSTRING(@input, 8, 8) --hard coded staart index and length
select SUBSTRING('[email protected]', CHARINDEX('@', @input) + 1, LEN(@input) - CHARINDEX('@', @input)) --without hard code using other string functions

output as follows

REPLACE (string expression, string to be replaced, replacement string) 

It is used to replace all the occurrences of a specific string to another string.

For example, 

DECLARE @input nvarchar(50)
set @input = '[email protected]'
select REPLACE(@input, '.COM', '.in')

The output is given below.

STUFF (string expression, start index ,length, replacement string) 

It is used to replace the string to the another string from a given string, but at the specified start index and length .

For example, 

DECLARE @input nvarchar(50)
set @input = '[email protected]'
select STUFF(@input, 12, 4, '.in')

The output is given below.