String functions in SQL Server- Part 2

Hello friends, In my last blog we learned about few useful string functions, now we are continuing the same topic in this article. We will get to know about few more useful string functions in SQL.

REPLACE(string_expression,Lookup_string,replacement_string):- Replace function take three parameters as input and as a result, it returns the original string after replacing the lookup_string with replacement_string within the original string.Lets take an example to understand this.

I am going to run below SQL statement and lets see the results.

Select REPLACE('Neeraj kumar Yadav','yadav','Sharma')

We can see in the above snapshot, as a result,we are getting string (“Neeraj kumar Sharma”) while our original string was “Neeraj Kumar Yadav”. Replace function replaced the lookup_value(“Yadav”) with replacement_string(“Sharma”).

REVERSE(String_Expression):- Reverse function takes one parameter as an input and returns the original string in reverse order. Let’s execute below SQL statement to see the results.

Select REVERSE('NEERAJ KUMAR YADAV')

We can see in the above screenshot, original string (“NEERAJ KUMAR YADAV”) has been reversed (“VADAY RAMUK JAREEN”).

RIGHT (String_Expression, Integer_Expression):- Right function take two parameters as an input and returns N no of characters from the right side of the original string. Where N=Integer_Expression value. Let’s execute below SQL script and see the results.

Select RIGHT('NEERAJ KUMAR YADAV',5)

In the below screenshot, we can see that as a result, we are getting “YADAV” and we can see that integer_expression value is 5, which means N=5, hence Right function picked the 5 letters from the Right side of the original string.

LEFT(String_Expression, Integer_Expression):- Left function works just opposite to the Right function. Instead of right side, it picks the N of characters from the left side of the original string. Where N=Integer_Expression value.

Select LEFT('NEERAJ KUMAR YADAV',6)

In the below screenshot, we can see that as a result, we are getting “NEERAJ” and we can see that integer_expression value is 6, which means N=6 , hence Left function picked the 6 letters from the LEFT side of the original string.

REPLICATE(String_Expression,Integer_Expression):- Replicate function take two parameters as an input and as result, it returns a string, after replicating the original string N times where N=Integer_expression.

Select REPLICATE('NEERAJ ',6)

We can see in the result set, original string was “NEERAJ”  and N=6, hence string has been replicated 6 times.  I have put space after last character(“J”) so that after replication, whole string should not merge. 

LTRIM(String_Expresion) :- LTRIM function removes the blanks from left side of the string expression.To understand this, I have written two SQL statements where I am concatenating 

two strings. First string has no blank spaces while second string has blank spaces at the left side(leading blanks).

Select 'Neeraj'+ '     Kumar'

Select 'Neeraj'+ LTRIM('     Kumar')

Below is the screen shot of the result.

In the above screenshot, we can see that all the spaces have been removed from the left side.

RTRIM(String_Expresion) :- RTRIM function removes the blanks from Right side of the original string. To understand this, I have written two SQL statements where I am concatinating two strings. First string has blank spaces at the right side(trailing blanks) while second string has no blank spaces.

Select 'Neeraj    '+ 'Kumar'

Select RTRIM('Neeraj    ')+ 'Kumar'

Below is the screen shot of the result.

In the above screenshot, we can see that all the spaces have been removed from the Right side of the string.

I hope this article will help you. Please let me know, if you have any query regarding this article.