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.

String functions in SQL Server- Part 1

Hello friends, today we will talk about different strings functions available in SQL Server. There are more than 20 string functions available in SQL Server but we are not going to learn each of them, rather we will learn about those functions which are being used frequently.  

Substring(Expression, Start, Length): - Substring function takes 3 parameters as input and returns value as per the input provided.  Let’s take an example to understand.

Expressionà Expression can be a table’s column or a string.

StartàStart parameter tells about the starting position of the string.

LengthàLength parameter tells about the length taken from the starting position.

Let’s run below SQL statement containing Substring function and see the results.

Select SUBSTRING('Neeraj Kumar Yadav',8,5)

If we look at the result, we are getting “Kumar” string as a result, lets understand this, character “K” is at 8th position in our original string,so our starting point is “K” and length parameter is 5, so it picked 5 characters from the starting point i.e. “K”, hence returned result as “Kumar”.

Lower (Expression):- Lower function takes one parameter as a input and return the character expression in lower case.Lets run the below SQL statement and see the results.

Select Lower('NEERaj Yadav')

We passed a string with few upper case letters and as a result we are getting whole string in lower case.

Upper(Expression):- Upper function reacts just opposite to the Lower function, it converts the lower case string expression to Upper case string expression. Let’s run below SQL statement and see the results.

Select Upper('NEERaj Yadav')

We passed a string with few lower case letters and as a result we are getting whole string in Upper case.

Quotename ([Character String],[Quote Character]):- Quotename function takes two parameter as a input and return the character string after concatinating the Quote Character. Lets take an example to understand.

Note:-Quote character can be single qoute(‘), double qoute(“)or bracket([,]), if Quote character is not specified, by defult SQL server takes bracket as quote character.

Lets run below SQL statement as see the result.

select Quotename('my name is neeraj',']') à with quote character

select Quotename('my name is neeraj')à without quote character

Stuff(Character_expression,Start,Length,Replace_Expression):- Stuff function takes four parameters as a input and returns character expression after inserting replace expression into the original character expression and starting position of the replace expression will be start parameter.

Lets take an example to understand.

Character_expressionà ‘Neeraj Kumar Yadav’

Startà3

Lengthà3

Replace_expressionà’lkanth’

Lets run below SQL statement and see the results.

Select STUFF('Neeraj Kumar Yadav',4,3,'lkanth')

We we look at the result below is the observation.

Original character expression=’Neeraj Kumar Yadav’

Start position is 4 and length is 3 and at fourth position in the orignal character expression “r” is the character and if we take length 3 that comes “Raj” hence “Raj” has been replaced with replace expression “lkanth”.

Len (String_expression):- Len function takes one parameter as input and returns count of characters in string expression.lets execute below SQL statemet containing Len function and see the results.

Select LEN('Neeraj')

We can see in the above snapshot result is coming as 6 which is true, no of characters in string “Neeraj” is 6.

SPACE(Integer_Expression):- Space function takes one parameter of integer type as a input and returns no of spaces according to supplied input parameter. Lets take an example to understand.

Lets run below SQL statement containing Space function and see the results.

Select 'Neeraj' + SPACE(1) + 'Yadav'à one space

Select 'Neeraj' + SPACE(2) + 'Yadav'àtwo space

Select 'Neeraj' + SPACE(3) + 'Yadav'àthree space

In the above screenshot, we can see the difference between 1,2 and 3 spaces respectively.

I have explained few strings functions in this post, we will learn about rest of the string functions in my forthcoming posts.