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 concatenating 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 default 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’
Lets run below SQL statement and see the results.
Select STUFF(‘Neeraj Kumar Yadav’,4,3,‘lkanth’)
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 original 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 statement containing Len function and see the results.
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.
1,025 total views, 1 views today