SQL Server: How to remove First and Last character from string

Introduction: In this article I am going to explain How to remove first and last character from string in sql server. We will also update trimmed string into table. 

In previous articles I have explained How to Split string from comma and get left and right part and How to Remove first or last character from string or column in sql server and Concatenate rows values as a comma separated string using for xml path and stuff and Query to get string between two characters or symbols and Count number of occurrences of character or word in a string in sql

Implementation:  Let understand by an example:

First lets consider we have a string having square brackets and we want to remove these brackets from the string i.e. we need to remove first and last character from string. Let’s write query to get the desired result.

DECLARE @strValue VARCHAR(MAX) = '[REMOVE THIS CLOSE BRACKET]'

SELECT SUBSTRING(@strValue,2,LEN(@strValue)-2) AS Result

Result would be as:

REMOVE THIS CLOSE BRACKET

As we can see brackets are removed

Now let’s see how to remove first and last character from the data in table.   

Create a table for demonstration using the following script

CREATE TABLE tbDemo

(

   strValue VARCHAR(50)

);

Insert some dummy data into this table.

INSERT INTO tbDemo

VALUES

('[ABC100]'),

('[ABC101]'),

('[ABC102]');

SELECT * FROM tbDemo

strValue

[ABC100]

[ABC101]

[ABC102]

As we can see data in the table is having brackets. We want to remove these brackets and update in table. We can do this by using the following update query :

UPDATE tbDemo SET strValue=SUBSTRING(strValue,2,LEN(strValue)-2)

Now let’s see updated data in table

SELECT * FROM tbDemo

strValue

ABC100

ABC101

ABC102

We got the desired output.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post