Remove all spaces from a string in SQL Server

Question

What is the best way to remove all spaces from a string in SQL Server 2008?

LTRIM(RTRIM('  a b ')) would remove all spaces at the right and left of the string, but I also need to remove the space in the middle.

Answer 1

You can use REPLACE() function to remove the spaces in the entire column or a string. Not only spaces, but you can also replace any character with another character. Here is the SQL query to remove all spaces from a string in SQL Server:

SELECT REPLACE(Column_Name, ' ', '')

I recommend checking out this SQL Tutorial to learn more about REPLACE() function.

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in an industry-recognized SQL training.

Answer 2

As per your question you want to remove the spaces from left,right and also from middle so we are going to use replace function that is inbuilt function in ms sql

Syntax:

Select replace(columnname,’ ‘,’’)

So query will be

Select replace(‘  a  b ’,’ ‘,’’  )