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.