Few days ago we learnt about how to use Date Function in SQL, In today’s post we will learn working of String Function in SQL. SQL string function is used for string manipulation.
Following are the commonly used inbuilt String function in SQL server.
In this post we will look into the use of all above String functions in detail
Lower function converts the char into lower case. In other term Lower () function convert the upper case into lower case.
In following example, I am passing capital letter characters in Lower () function, this function then convert these characters into lower case.
select lower('MANOJ') Result: manoj
If we have couple of character in uppercase and couple of in lower case, then this function return string into lowercase without any error.
Let say we have a string ‘Learn SQL’
select lower('Learn SQL') Result : learn sql
So that is the use of Lower Function. Next function is Upper () Function.
2. Upper ()
Upper () function is opposite to lower () function. This function used to convert lower string into uppercase. This function returns string in Capital order.
Let Say we have string ‘learn sql server’.
select Upper('learn sql server') Result : LEARN SQL SERVER
So this is how we case use Upper and Lower function.
Ltrim () function returns the string by truncating space at a start of a string in left most side. It will not truncate if space present in right hand side. So Ltrim () function make sure that, it truncate the space if it present at a start of string.
In following example, I have intentionally added spaces in start of a string. It will truncate the spaces that occur at start of a string.
select ltrim(' learn sql server') Result :learn sql server
So you can clearly see, the space has truncated from start of a string, but it has not truncated spaces that occur in middle or right side of a string.
Rtrim() is opposite to Ltrim function. It will truncate the space on the right hand side.
In following example, I am adding space at right hand side.
select ltrim('learn sql server ') Result : learn sql server
So RTRIM () has removed the space at right hand side of a string.
In another scenario if we have require to truncate both left and right side spaces.
Let say, we have following string.
' learn sql server '
To truncate the spaces in both sides, TRIM part will trim the spaces. Using LTRIM and RTRIM the trim part will remove the space.
select ltrim(Rtrim(' learn sql server ')) Result : learn sql server
So this is how we can use the LTRIM and RTRIM function.
Replace function take 3 argument.
• First string is a original string
• Second argument is what we want to replace in original string
• And third argument, what we want to replace with.
It will take second string to search for in first string and replace in third string if it find the string in original string. Let say we want to replace “good day” to “good night”
select Replace('Good day','day','night') Result : Good night
It uses to replicate a string in given number of time. It takes two arguments.
• First argument has to be string that you want to repeat
• Second argument should be number of time for repeat a string
select Replicate('Good day',2) Result : Good dayGood day
As you can see Good day has replicated two times.
Reverse function use to reverse a string.
select Reverse('SQL') Result: LQS
This function returns the character number position of a specified string. Or it may used to find if specific character present in a string or not.
It start search from the first letter or value of the text.
Let say, we need to find if Letter ‘N’ is present in text ‘Manoj’.
select charindex('N','Manoj') Result: 3
So that letter has been found to be located in third position of the word. It will return 0 if specific letter not found
Use to determine the number of character present in string.
select len('SQL') Result: 3
So Len() function return number of character of word SQL.
It uses to get part of a string. In some scenario we need to find part of a string from long string in that case we can use Substring() function.
It takes 3 arguments.
• First argument is Original String
• Second argument is Offset position
• Third argument is Number of character we need after offset position
Let say, we need to get “SQL” from text “Learn SQL Server”. So first we need to determine our offset position of “S” in text “SQL”. So that is 6 including space in text “Learn SQL Server”
Third argument would be number of character after Offset position that is 4.
select Substring('Learn SQL Server',6,4) Result : SQL
So this is how we can get part of a string from a string using Substring function.
It return the specified number of character from the left hand side of a given character expression or it allow to get part of string that starting from left most character.
Let say, we want to extract “Learn” from a string “Learn SQL Server”
select left('Learn SQL Server',5) Result: Learn
You can clearly see that we are getting “Learn” word form “Learn SQL server” that start from left most character.
It return the specified number of character from the right hand side of a given character expression. Or it allow to get part of string that starting from right most character
Let say we need to extract “Server” from word “ Learn SQL Server”.
select right('Learn SQL Server',6) Result : Server
So now right function has extract “Server” from string “Learn SQL Server”
Hence we have learned basic use of string function in SQL.