One of my colleague asked me this question, Is there a way to generalize a function to remove last two (n) characters from a column if the column doesn’t has a fixed length. Yes it can be as below
Syntax: LEFT (ColumnName,LEN(ColumnName) – n)
The function calculates the length of the string and removes last n characters using LEFT() function.
Here is the kicker I wanted to share with you, Is it possible to flip the above function using RIGHT() to remove first n characters from a Column(string), yes it is possible:-)
Syntax: RIGHT (ColumnName,LEN(ColumnName) – n)
I made up a sample data using AdventureWorks - Transaction table in my SQL Server 2014 version. Imagine you have the TransactionID and ProductID concatenated as TranProdID column. Removing the last two characters will give you the TransactionID and removing the first three characters will give you the ProductID.
The query can be written as
SELECT TranProdID, LEFT(TranProdID,LEN(TranProdID)-2) as TransactionID, RIGHT(TranProdID,LEN(TranProdID)-3) as ProductID FROM [SQLCAST2014].[Production].[Transaction]
Here is the snippet from my machine,