SQL server consists of two types of string functions:

1. System defined String function
   
2. User defined String Function
   

System functions are functions which are present in SQL server by default as shown below:

We can use system function based on our requirement. When we want to perform any aggregate than we use aggregate function like Max(), Avg() etc.

In this article I will be discussing about String Functions present in SQL Server.

ASCII(expression):

ASCII string function returns the ASCIII value of the first character (leftmost character) of the expression value as shown below:

SELECT ASCII(‘A’)

Output: 65

SELECT ASCII(‘a’)

Output: 97

It takes a char or varchar expression and returns an ASCII code value integer.

Char(integer expression)

Char() is an opposite of ASCII() function. Char() function converts a integer value to a Character. The Integer expression should be between 0-255.

select Char(65)

Output: A

select Char(97)

Output: a

It take a tiny int value which varies from 0-255 and returns a char(1).

LTRIM(Character Expression)

LTRIM() removes the white space on the left side of the given character expression.

In order to demonstrate the same lets taken an example of following:

print ‘ SAILLESH’

print LTRIM(‘ SAILLESH’)

Output:

RTRIM(Character Expression)

RTRIM() method is used to remove right side whitespaces of the given character expression.

print ‘SAILLESH ‘

print RTRIM(‘SAILLESH ‘)

Output:

Using LTRIM() AND RTRIM() to remove both left side and right side whitespaces from a character expression.

In business application there are scenarios where we need to display full name to the UI, due to not proper business logic names may get stored with whitespaces which may lead to UI discrepancy. So in order to solve the same we can make use LTRIM and RTRIM to remove the whitespace and to be the shown to the UI as shown below:

So we have a table which consists of only Names i.e. FirstName, MiddleName and LastName.

We can see due improper server side code values with whitespace are getting stored in db. So now in order to handle the same while showing to the UI we need to use RTRIM and LTRIM method to remove whitespaces.

select RTRIM(LTRIM(firstname)) + ‘ ‘ +

RTRIM(LTRIM(SecondName)) + ‘ ‘ +

RTRIM(LTRIM(LastName)) AS FULLNAME

FROM tblPersonDetails

Output:

UPPER (character expression)

UPPER() method returns the lowercase character expression to UPPER case character expression as shown below:

select UPPER(‘saillesh’)

Output:

I found Upper method useful when I want to apply a filter as shown below:

select FirstName from tblPersonDetails

where RTRIM(LTRIM(UPPER(SECONDNAME)))=’SINGH’;

Here I am changing the ColumnValue SecondName to uppercase and matching the same with ‘SINGH’.

It takes a varchar or nvarchar expression and returns a varchar or nvarchar expression converted to UpperCase.

Lower(character expression)

Lower() used to return the uppercase expression to lowercase character expression.

— Syntax for SQL Server

select LOWER(‘SAILLESH’)

Output:

It takes a character expression of type varchar or nvarchar and returns it to the lower case character expression.

REVERSE(Character Expression)

Reverse() function is used to reverse the character expression as shown below:

— Syntax for SQL Server

select REVERSE(‘SAILLESH’)

Output:

Len(Character Expression)

Len function returns the number of character present in the character Expression parameter. Len function is majorly used in stored procedure to apply the business logics.

Calculating length of each firstname and there total length.

select RTRIM(LTRIM(FirstName)),len(RTRIM(LTRIM(FirstName))) as LENGTH from tblPersonDetails

Output:

Len() ignores the right side whitespace at the end of the expression as shown below:

select LTRIM(FirstName),len(LTRIM(FirstName)) as LENGTH from tblPersonDetails

Output:

Left(characterExpression, length)

Left function returns the left most specified number of character from the character Expression.

Syntax: Select Left(characterExpression,NumberOfCharacterToBeReturned)

SELECT LEFT (‘SAILLES’,5)

Output: SAILL

Right(characterExpression, length)

Right method returns the right most characters from the specified number of character from the character expression.

Syntax: Select Right(characterExpression, NumberOfCharacterToBeReturned)

SELECT RIGHT (‘SAILLES’,5)

Output: ILLES

CharIndex()

CharIndex() method returns the starting point index of the specified character expression.

CharIndex method takes three parameters:

1. Search expression: Expression to find
   
2. Expression to be searched
   
3. Start Location
   

Syntax:

select CHARINDEX(‘l’,’saillesh’,0)

Output:

4

SubStr()

Substr() method returns a Substring i.e. part of a character expression. SubStr() method takes a three parameters Expression to be SubStr, Starting Position, total length to be traversed.

Syntax:

SELECT SUBSTRING(‘SAILLESH’,1,5)

Showing all the email address with only domain name:

We have the above mentioned Table and we will show only the domain part of the email. In order to achieve the same let make use of CharIndex and SubString to achieve the same.

select SUBSTRING(email,CHARINDEX(‘@’,email,0)+1,LEN(email)-CHARINDEX(‘@’,EMAIL)) from TBLPERSON

Output: