Post
Ask Question

String Functions in SQL Server

This article explains some of the most commonly used string functions in SQL Server.

The functions in SQL Server are divided into the following two categories:

  1. System Defined Functions
  2. User Defined Functions

We will explain System Defined Functions.

If you want to see all the available system functions, expand the user-defined database you created then expand programmability then expand functions then expand system functions.



To understand the functions available in the String Functions category, expand String Functions.



Now let's look at each of this functions practically.

ASCII function

The ASCII function returns the ASCII code of the given character passed in as an expression.

Syntax



Look at the parameter this ASCII function expects, it is expecting a parameter argument of type nvarchar and returns an integer value back.

Let's say we want the ASCII code of value 0.

 

  1. SELECT ASCII('0');  

 



Let's say we want the ASCII code of small letter "a" and capital letter "A".

 

  1. SELECT ASCII('a')  

 


  1. SELECT ASCII('A')  

 



CHAR function

To convert an ASCII code into a character, we use the CHAR function. The integer value we pass in, must be between 0 and 255.

Syntax



Look at the parameter this function expects, it is expecting an argument of type integer that will return a single character back.

Let's say we want the character value of ASCII code 48. 

  1. SELECT CHAR(48);  

 



Now let's look at another example where we want to print numbers from 0 to 9 using the CHAR function.

As we know the ASCII code for 0 is 48. So, what we can do is we can add 9 to 48 that will provide us the ASCII for values 0 to 9, in other words the ASCII code for 0 is 48, ASCII code for 1 is 49 and ASCII code for 9 is 57.

  1. --Declare a variable of type integer  
  2. DECLARE @Input INT  
  3. --initialize this variable with a value 48 which is an ASCII code 0.  
  4. SET @Input = 48  
  5. --specify a condition, where Input value must be less than or equal to 57  
  6. --because 57 is the ASCII code for 9.  
  7. WHILE(@Input <=  57)  
  8. BEGIN  
  9. --Print the value  
  10. PRINT CHAR(@Input)  
  11. --Increment the @Input variable by 1  
  12. SET @Input = @Input + 1  
  13. END  
  14.   
  15.   
  16. DECLARE @Input INT  
  17. SET @Input = 48  
  18. WHILE(@Input <=  57)  
  19. BEGIN  
  20. PRINT CHAR(@Input)  
  21. SET @Input = @Input + 1  
  22. END  
Execute the preceding query.



LTRIM function

The LTRIM function trims the blank space from the left side of the column rows.

In my database I have this table:



The following is the query for creating this table:

  1. CREATE DATABASE dbStringFunction;  
  2. GO  
  3. USE dbStringFunction;  
  4. GO  
  5.   
  6. CREATE TABLE tblStudents(  
  7. Id INT IDENTITY(101,1) PRIMARY KEY,  
  8. FirstName NVARCHAR(50),  
  9. MiddleName NVARCHAR(50),  
  10. LastName NVARCHAR(50),  
  11. Gender NVARCHAR(10),  
  12. Email NVARCHAR(100)  
  13. )  
  14.   
  15. INSERT INTO tblStudents VALUES  
  16. ('Harry        ','      James      ','Potter   ','Male','[email protected]'),  
  17. ('             Sara','','Conner','Female','[email protected]'),  
  18. ('Samuel','','Fisher','Male','[email protected]'),  
  19. ('Max            ','','       Payne       ','Male','[email protected]'),  
  20. ('         Lara                ','','Croft','Female','[email protected]'),  
  21. ('         Aiden ','','           Pearce','Male','[email protected]')  
In the column FirstName, MiddleName and LastName there are some blank white spaces to their left.

To remove those spaces we can use the LTRIM function.

  1. SELECT LTRIM(FirstName) AS FirstName, LTRIM(MiddleName) AS MiddleName,LTRIM(LastName) AS LastName FROM tblStudents;  
Pass the column as a parameter argument in the LTRIM function.

The following is output of the preceding query:



Now let's say for some reason, we want these three columns to be merged together and displayed as FullName.

  1. SELECT LTRIM(FirstName) AS FirstName, LTRIM(MiddleName) AS MiddleName,LTRIM(LastName) AS LastName, (FirstName+' '+MiddleName+ ' '+LastName) AS FullName FROM tblStudents;  
The following is output of the preceding query:



Let's remove the blank spaces from the left side.

  1. SELECT LTRIM(FirstName) AS FirstName, LTRIM(MiddleName) AS MiddleName,LTRIM(LastName) AS LastName, (LTRIM(FirstName)+' '+LTRIM(MiddleName)+ ' '+LTRIM(LastName)) AS FullName FROM tblStudents;  
The following is output of the preceding query:



Look at the output we got. We removed the blank spaces from the left but there are a few spaces in the right side too and to remove these blank spaces from the right side we can use the RTRIM function.

RTRIM function

To remove blank space from the right-side of the given data, use the RTRIM function.

Wrap the LTRIM function inside the RTRIM function.

  1. (RTRIM(LTRIM(FirstName))  
  2. SELECT LTRIM(FirstName) AS FirstName, LTRIM(MiddleName) AS MiddleName,LTRIM(LastName) AS LastName, (RTRIM(LTRIM(FirstName))+' '+RTRIM(LTRIM(MiddleName))+ ' '+RTRIM(LTRIM(LastName))) AS FullName FROM tblStudents;  
Execute the query:



Lower and Upper function

To change the case from lower to upper, use the UPPER function and to change the case from upper to lower, use the Lower case function.

  1. --Upper case  
  2. SELECT UPPER('c-sharpcorner');  


  1. --Lower case  
  2. SELECT LOWER('C-SHARPCORNER')  



LEN function

To get the length of a given string expression use the LEN function. This LEN function excludes the blanks at the end of the expression, meaning it ignores the right hand side blank spaces.

  1. SELECT FirstName,LEN(FirstName) FROM tblStudents;  



Look the output. The total length for Harry is 5; that is fine. But look at the total length of Sara, which is 17. The actual length of Sara is 4.

So, how can we get the actual length?

To get the actual length we need to wrap the LTRIM function inside the LEN function.

  1. SELECT FirstName,LEN(LTRIM(FirstName)) FROM tblStudents;  
Execute the query.



LEFT function and RIGHT function

To get the specified number of characters from the left side of the given character, use the LEFT function whereas to get the specified number of characters from the right side of the given character, use the RIGHT function.

LEFT function



Look at the parameter this LEFT function expects, the first parameter expects an argument of type nvarchar and the second parameter expects an integer value that will return the number of characters from the left hand side.

  1. SELECT ('C-SharpCorner.com');  

Let's say from the preceding expression, we want only the first 7 characters from the left side.

  1. SELECT LEFT('C-SharpCorner.com',7);  



Now let's say this time we want 10 characters from the right side.

  1. SELECT RIGHT('C-SharpCorner.com',10);  



CHARINDEX function

To get the starting position of the specified string expression or data, use CHARINDEX function.

Let's say from the table column below, we want the char-index of "@".

  1. SELECT Email FROM tblStudents  

The first parameter expects an expression that this function must look for from the second parameter value and the third parameter is the starting positing from where this function starts looking for. But it is optional by default.

  1. --CHARINDEX  
  2. SELECT Email, CHARINDEX('@',Email) AS IndexPosition FROM tblStudents  
We are looking for the index position of "@" in the Email column.



SUBSTRING function

To return a part of a given string expression, use the SUBSTRING function.


In the first parameter pass the expression from which we want a part of the string.

In the second parameter pass the starting position from where this function will return a part of the string.

In the third parameter pass the length of the string value that you want to return.

  1. --SUBSTRING  
  2. SELECT SUBSTRING('Hello and welcome',1,5);  
Execute the preceding query.



In the output we got the first five characters back from the beginning.

Let's say we want welcome to be returned.

  1. --SUBSTRING  
  2. SELECT SUBSTRING('Hello and welcome',11,7);  
Pass the start position as 11 to ignore Hello and substring.

Execute the query.

Let's look at another example of the substring function.

In the tblStudents table I have these records.

  1. SELECT * FROM tblStudents  


Let's say for some reason we want to retrieve only the domain name from the email column and with that we want to count the number of students using a specific domain and group them by the domain.

  1. SELECT COUNT(Email) AS [Total Students], SUBSTRING(Email,CHARINDEX('@',Email)+1,LEN(Email) - CHARINDEX('@',Email)) AS DomainName FROM tblStudents  
  2. GROUP BY SUBSTRING(Email,CHARINDEX('@',Email)+1,LEN(Email) - CHARINDEX('@',Email))  
Look at this expression.
  1. SUBSTRING(Email,CHARINDEX('@',Email)+1,LEN(Email) - CHARINDEX('@',Email)) AS DomainName  

How it works

  1. In the substring function we passed Email as a first parameter from which we want to retrieve a part of the string.
  2. In the second parameter we passed the CHARINDEX function. This function expects two mandatory parameters. We passed "@" in the first parameter and in the second parameter we passed the Email column in which this charIndex function will look for the Index position of "@". Once this function returns the index position of "@", we want to add 1 to the value because we don’t want "@" to be displayed with the domain name.
  3. In the third parameter of the Substring function, we passed an expression.
    1. LEN(Email) - CHARINDEX('@',Email)  

What this expression will do is, it will subtract the total length of the email by the index position of "@" that will provide the domain name.

Execute the query.



REPLICATE function

If you want to repeat a given string for a specified number of times.



In the first parameter pass the expression that you want to repeat.

In the second pass the number of times you want to repeat the expression.

Let's look at an example of it.

  1. SELECT REPLICATE('C-SharpCorner',3)  
Execute the query.



In the output C-SharpCorner is repeated 3 times.

Let's look at a realistic example where we can use the Replicate function.

In websites like justdial.com, they never reveal the full email address of the users for some security reasons.



Let's see how to do the same thing using the Replicate function.

In tblStudents we have an Email column.



Write the following query.

  1. SELECT Email, SUBSTRING(Email,1,2) + REPLICATE('*',5) + SUBSTRING(Email,CHARINDEX('@',Email),LEN(Email) - CHARINDEX('@',Email)+1)  
  2. FROM tblStudents  
Execute it.



How it works

  1. Look at the first expression, SUBSTRING(Email,1,2). In this expression we passed the Email column in the first parameter as an expression. In the second parameter we passed the start position from where this substring function will take a part of the string from the email column and in the third parameter we passed the length.
  2. Look at the second expression, REPLICATE('*',5). We concatenated the substring with a replicate value and currently these two expressions will give us ha*****.
  3. Look at the third expression:
    1. SUBSTRING(Email,CHARINDEX('@',Email),LEN(Email) - CHARINDEX('@',Email) + 1)  
    We concatenated the replicate expression with this expression. This expression will give us the domain name that we already discussed in the SUBSTRING function. When all the three expressions are concatenated with each other it will give us ha*****@gmail.com.

SPACE function

Let's say we want to display the FirstName and LastName as FullName and for that we can write the query below.

  1. --SPACE  
  2. SELECT RTRIM(LTRIM(FirstName)) + RTRIM(LTRIM(LastName)) FROM tblStudents;  
Execute the query.


Look at the output we got. We got both the first name and last name. But let's say we want to add a space between FirstName and LastName.

  1. The first way to do that is by concatenating a white space in single quote.
    1. SELECT RTRIM(LTRIM(FirstName)) + ' ' +RTRIM(LTRIM(LastName)) FROM tblStudents;  
    Execute the query.



  2. The other way is to use the SPACE function. Pass the number of spaces you want as a parameter.
    1. SELECT RTRIM(LTRIM(FirstName)) + SPACE(3) +RTRIM(LTRIM(LastName)) FROM tblStudents;  
    Execute the query.

PATINDEX function

PATINDEX function returns the starting position of the first occurrence of a pattern from a specified expression.

The first parameter expects a pattern and the second parameter expects an expression in which this function will look for the pattern.

  1. --PATINDEX  
  2. SELECT Email, PATINDEX('%@live.com',Email) FROM tblStudents WHERE PATINDEX('%@live.com',Email) > 0  



Note: Both PATINDEX and CHARINDEX are similar. But in CHARINDEX we cannot specify wildcards.

REPLACE function

The Replace function is used to replace the specified value with another value in a given expression.



In the table above, let's say we want to replace the domain of Sara from yahoo.com to live.com.

Let's see how to do it.

  1. SELECT FirstName, Email, REPLACE(Email,'@yahoo.com','@live.com') FROM tblStudents  
  2. WHERE Id = 102  
In the first parameter pass the expression in which you want the change.

In the second parameter pass the part of the first parameter expression that you want to change.

In the third parameter pass the expression that you want to replace the part of the expression with.

Execute the query.



Summary

In this article we learned some of the useful built-in string functions.

I hope you like it. Thank you.


Similar Articles