Remove all whitespace from string in SQL Server

In previous article, I have mentioned how to Delete (Drop) Database in SQL Server, but now in this article, I have mentioned examples of how we can remove whitespace or space from string in SQL Server.

Using SQL Replace

SQL Replace() is used to Replace all occurrences of a specified string value with another string value.

Here is the syntax to use it

REPLACE(main_string, old_string_to_remove, new_string_to_add)

So suppose, we have a string "Hello World welcome" and we want to remove all spaces from string, then SQL Query for it would be

SELECT REPLACE('Hello World welcome', ' ', '');

Output:

Above Query will work in all situtations and easy to use.

Replace all columns in table

If you want to replace all columns of a particular table, then you can have sample query as below

UPDATE TableName
SET ColumnName = REPLACE(field_name , 'oldstring', 'newstring') 
WHERE ColumnName LIKE ('TestField%');

Remove or Trim Trailing Spaces from string

In the above examples, we are removing all spaces from string in SQL, but if you want to remove trailing spaces from string (first and last space) in SQL Server, then you can use LTRIM and RTRIM

SELECT LTRIM(RTRIM('    Hello World welcome    '))

Output:

Hello World welcome

Remove all extra spaces in-between words

Suppose, you have more than one spaces in between words, but you want to keep only 1 space between words of a string, basically you want to remove extra space in text, then you can use below SQL Query

SELECT LTRIM(RTRIM(REPLACE(REPLACE(REPLACE('    Hello    World        welcome    ',CHAR(32),'()'),')(',''),'()',CHAR(32))))

So it will have output like below

That's it, hope it helps, if we have left any scenerio to remove space in SQL Server, you can comment it down.

You may also like to read:

Stored procedure in sql server (With Example)

SQL server connection string examples in C# & XML

Free SQL Server Reporting Tools

Import data from Excel to SQL Server

What is SQL Query to get dates between two dates?

SQL Comments (Comment in SQL Query)

Difference between SQL and NoSQL (SQL vs NoSQL)

How to back up SQL Server database ? (Various Ways explained)

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

5+ Best Visual Studio Alternatives