Post
Ask Question

Removing the First and Last Character From a Table Column in SQL Server 2012

In this article I provide a quick overview of the various Queries to remove the first and last character from a string in SQL Server. To remove the first and last character, we use as an example a simple string and also use a column of a table which contains some name. To do that we can create various queries using SQL functions. So let's have a look at a practical example of how to remove the first and last character from a string in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 
The following are the various queries to remove the first and last character from a string.
 

Remove first character from string in SQL Server

 
First we remove the first character from a string. Suppose you take a string Rohatash. The output should be ohatash.
 
1. Using the SQL Right Function
 
Example
  1. Declare @name as varchar(30)='Rohatash'  
  2. Select right(@name, len(@name)-1) as AfterRemoveFistCharacter  
Output
 
Remove-first-character-in-SQL-Server.jpg
 
2. Using the Substring Function
  1. Declare @name as varchar(30)='Rohatash'  
  2. Select substring(@name, 2, len(@name)-1) as AfterRemoveFirstCharacter  
Output
 
Remove-first-character-using-substring-function-in-SQL-Server.jpg
 

Remove last character from a string in SQL Server

 
Now remove the first character from the string. Suppose you have the string Rohatash. The output should be Rohatas.
 
1. Using the SQL Left Function
  1. Declare @name as varchar(30)='Rohatash'  
  2. Select left(@name, len(@name)-1) as AfterRemoveLastCharacter  
Output
 
Remove-last-character-in-SQL-Server.jpg
 
2. Using the Substring Function
  1. Declare @name as varchar(30)='Rohatash'  
  2. Select substring(@name, 1, len(@name)-1) as AfterRemoveLastCharacter  
Output
 
Remove-last-character-using-substring-function-in-SQL-Server.jpg
 

Remove first and last character from a string in SQL Server

 
Now we remove the first and last character from a string. Suppose you have the string Rohatash. The output should be ohatas. 
 
1. Using the SQL Left and Right Functions
  1. Declare @name as varchar(30)='Rohatash'  
  2. Declare @n varchar(40) =left(@name, len(@name)-1)  
  3. Select right(@n, len(@n)-1)  
Output
Remove-first-and-last-character-using-left-right-function-in-SQL-Server.jpg
 
The above query can be defined as follows:
  1. Select LEFT(RIGHT('rohatash', len('rohatash')-1),len('rohatash')-2)  
2. Using the Substring and Len Functions
  1. Declare @string varchar(50)  
  2. SET @string='rohatash'  
  3. Declare @len varchar(50)  
  4. Declare @middle varchar(50)  
  5. Select @len=LEN(@string)  
  6. Select @middle=substring(@string,2,(@len-2))  
  7. Select @middle  
Output
 
Remove-first-and-last-character-using-substring-function-in-SQL-Server.jpg
 
The above query can be defined as follows:
  1. Declare @name as varchar(30)='Rohatash'  
  2. Select substring(@name,2,(LEN(@name)-2))   

Remove first and last character from table column in SQL Server

 
Now we create a table named employee using:
  1. Create table Employee  
  2. (  
  3. EmpID int,  
  4. EmpName varchar(30),  
  5. EmpSalary int  
  6. )  
The following is the sample data for the employee table:
 
Employee-table-in-Sql-Server.jpg 
 
1. Using the Substring and Len Functions
  1. Select EmpName, substring(EmpName,2, len(EmpName)-2) from [Employee]  
Output
 
Remove-first-and-last-character-using-substring-function-from-table-column-in-SQL-Server.jpg
 
2. Using the SQL Left and Right Functions
  1. Select EmpName, LEFT(RIGHT(EmpName, len(EmpName)-1),len(EmpName)-2)  from [Employee]  
Output
 
Remove-first-and-last-character-using-leftand right-function-from-table-column-in-SQL-Server.jpg


Similar Articles