SQL Server Remove Character From String

In this SQL Server tutorial, you will understand SQL Server remove character from string.

You will learn about the reasons to remove the character from a string, then functions such as REPLACE(), LTRIM(), and RTRIM() that you can use to remove the character from the given string.

Table of Contents

SQL Server Remove Character From String

Removing the characters from the string is crucial for different reasons, as it contributes to data integrity, consistency, and usability.

Here are some of the reasons that are required to remove the character from the string.

As you know, data comes from different or diverse sources and can include unwanted characters such as special characters, spaces, line breaks, etc. If you are processing data or generating reports, these characters can interfere or lead to wrong reports or analyses.
The data should be in a consistent format for data analysis and reporting. Dates, phone numbers, and currency values may need to be formatted in a standard way to ensure uniformity of the data across the database.
You can improve the quality of your data; if there are unwanted characters in your string, it can lead to poor data quality or also affect the reliability of the data. So, by removing these characters, you can improve the data quality and enhance the reliability.
So when you have a string without unwanted characters, then you can parse that string easily. Also, you can extract meaningful information.
Sometimes, you need to integrate the data from multiple places, so inconsistencies in the string format arise automatically due to unwanted characters. Removing these characters from the string helps standardise the data and make the integration smoother.
Removing the unwanted characters from the string also helps optimise the search and query performance.

Now you know how important it is to remove the character from the strings. Let’s see the methods that you can use to remove the character from the string in your table column.

SQL Server Remove Character From String using Replace Function

REPLACE() function in SQL Server replaces the substring of the given string based on the specified new string value.

Using this function, you can remove any character from the given string, whether a special character or the substring part of that string.

For example, let’s say you have a string ‘United States’ and want to remove a specific character such as ‘t’ from that string.

So, use the below query and understand how it works.

SELECT REPLACE('United States', 't','') AS Output;

SQL Server Remove Character From String using Replace Function

As you can see in the above output, the REPLACE() function returns the result as Unied Saes after removing the character ‘t’ from the string ‘United States’.

If you know about the REPLACE() function, you probably know how the function removes the character from the string.

If you don’t know, let me explain that the REPLACE() function does not remove the character from the string; in reality, it replaces that ‘t’ character with this ” double single quote without space.

Now you have another string, ‘I #live## in USA#’, but as you can see, it contains the special character’s hash (#), which makes no sense. Now, you want to remove that character from the string.

For that, use the below query.

SELECT REPLACE('I #live## in USA,#', '#','') AS Output;

SQL Server Remove Character From String using Replace Function with Special Character

From the above output of the query, you can see that the REPLACE() function removed the specific # character from the string ‘I #live## in USA#,’ and returns the new string ‘I live in USA,’.

So here, when you execute the above query, the REPLACE() function searches for the # character in the string, ‘I #live## in USA#,’ and removes that with the specified string, which is ” in this case.

You can also remove the unwanted characters from the table column using the REPLACE() function.

This is how to use the REPLACE() function in SQL Server remove character from string.

SQL Server Remove Character From String Using the LTRIM Function

So, if you want to remove the specific character from the beginning of the string, you can use the LTRIM() function.

For example, if you have the string ‘#%Canada’ and want to remove the character #% from the beginning of that string, then use the below query.

SELECT LTRIM('#%Canada','%#');

SQL Server Remove Character From String Using the LTRIM Function

Look at the above output; the LTRIM() function removed the specified characters ‘#%’ from the beginning of the string ‘#%Canada’ and output the result Canada, which is good in readability.

Remember, this function can only remove the specified change from the beginning of the string.

This is how to remove the specific character from a string in SQL Server from the beginning of the string.

This is how to use the LTRIM() function in SQL Server remove character from string.

SQL Server Remove Character From String Using the RTRIM Function

So, if you want to remove the specific character from the end of the string, you can use the RTRIM() function.

For example, suppose you have the string ‘United States of America–‘ and want to remove the character hyphen (-) from the end of that string, then use the below query.

SELECT RTRIM('United States of America--','-');

SQL Server Remove Character From String Using the RTRIM Function

Executing the above query removes the specified characters (-) from the end of the string ‘United States of America–‘ and returns the result as ‘United States of America’.

You can also apply the above function to remove the specific character from the table column.

This function only removes the specified character from the end of the string. This is how to use the RTRIM() function in SQL Server remove character from string.

Conclusion

In this SQL Server tutorial, you have removed the character from the string using the REPLACE() function in SQL Server. Then, you removed the character from the beginning of the string using the LTRIM() and the character from the end using the RTRIM() function.