String function in SQL

In the last article, I was explain about 

Arithmetic functions

Now In this article I am going to explain string function in SQL.

String Functions

The following are the string functions.

ASCII (s)

It returns the ASCII value of the left character in the given string.

Example

select ASCII ('A')              Ans: 65
select ASCII (.ABC')        Ans: 65

Char (n)

It returns the character representing the given ASCII value.

Example

select Char (66) Ans: B

Lower (str)

It converts string into lower case.

Example

select Lower ('SHARAD') Ans: sharad

Upper (str)

It converts string into upper case.

Example
select Upper ('sharad') Ans: SHARAD

Len (str)

It returns the length of the given string.

Example

select Len (‘Sharad’) Ans: 6

Left (str, n)

It returns specified 'n' no. of characters from the left side of the given string.

Example

select Left ('Sharad', 4) Ans: Shar
select * from Emp where Left (ename, 3) Ans: Sha

Right (str, n)

It returns specified n no. of characters from the right side of the given string.

Example

select Right ('Sharad', 4) Ans: arad

Ques  Write a query to get a list of employees whose names end with the character 'rd'?
Ans select * from Emp where Right (ename, 2)='RD'

Ques Write a query to get a details of employees whose names 3rd & 4th characters are 'IT'?
Ans select * from Emp where Right (Left (ename), 4), 2)='IT'

Substring (s, START, length)

It returns a part of the string from the given string 's', From the specified start character and no. of characters specified as length.
Example

select Substring ('Hello', 1, 3)  Ans Hel
select Substring ('Hello', 2, 3) Ans ell

Ques Write a query to get a details of employees whose names 3rd & 4th characters are 'IT'?
Ans select * from Emp where Substring (ename, 3, 2)='IT'

Reverse (str)

It returns the given string in the reverse order.

Example
select Reverse ('shri') Ans: irhs

LTRIM (str)

It returns a string length eliminating any empty characters if present in the left side of the given string.

Example

select Len ('..shar')      Ans: 6
select LTRIM ('..shar') Ans: 4

RTRIM (str)

It returns a string length eliminating any empty characters if present in the right side of the given string.

Example

select Len ('Shar..')     Ans: 4
select RTRIM ('Shar..') Ans: 4

NOTE

RTRIM (str) function is not much useful because any empty characters in the end of the string is not considered.

Replace (str, search, replace)

It returns a new string by replacing each occurrences of the ‘search’ character with ‘replace’ character in the given string 'str'.

Example

select Replace ('Hello', 'L', 'K') Ans: HeKKo

Ques Write a select statement which retrieves the details of the customers whose name is 'SHARAD GUPTA'?
Ans select * from Emp where Replace (ename, ' ', '') Ans: SHARADGUPTA

CharIndex (search, str [,start]

It is used for finding the index position of the search character within the given string 's'.

Example
select CharIndex ('E', 'Hello')                 Ans: 2
select CharIndex ('o', 'Hello World')     Ans: 5
select CharIndex ('o', 'Hello World', 6) Ans: 8

NOTE

1 In the above case even if the string contains multiple ‘o’ characters in it, it always search and returns the index of the first character because the search starts from the first character.
2  'Start' is used for specifying the location from where the search has to be started.
select CharIndex ('X', 'Hello') Ans: 0
 
Ques Write a query to get the list of all the employees whose name contains character 'M' in them?
Ans select * from Emp where CharIndex ('M', ename) > 0

STUFF (str, start, length, Replace)

It replaces the given string with the Replace string from the specified starting character to the no. of characters specified as length.

Example

select STUFF (‘AXXBXXC’, 2, 2, ‘ZZ’)             Ans: AZZBXXC
select STUFF (‘AXXBXXC’, 2, 2, ‘MNO’)        Ans: AMNOBXXC

SOUNDEX (str)

The function returns a 4 digit alphanumeric string for each given string value.

Example
select Soundex ('color')                    Ans: C460
select Soundex ('colour')                 Ans: C460

1 It performs the calculation in the way the strings are sounded (or) pronounced.
2 We can make use of this function to perform comparisons between two strings which are sounded in the same way but has different spelling.

Like

select * from Emp where Soundex (ename) = Soundex ('Smyth')

NOTE

While applying the Soundex function it has to be applied on both sides of the
condition.