Remove all spaces from a string in SQL Server

1.7K Asked by Bhaanumatishukla in SQL Server , Asked on Apr 6, 2021

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.

Answered by Bhaanumati shukla

To solve the “SQL remove spaces” query, 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 remove spaces from a string in SQL Server:

  SELECT REPLACE(Column_Name, ' ', '')

Your Answer

Answer (1)

Ranjana

To remove all spaces from a string in SQL Server, you can use the REPLACE function. The REPLACE function allows you to replace all occurrences of a specified substring within a string with another substring.

Here’s the syntax for using the REPLACE function to remove spaces:

  REPLACE(string_expression, ' ', '')

Example Usage

If you have a table named ExampleTable with a column TextColumn containing strings with spaces, you can remove the spaces as follows:

  SELECT REPLACE(TextColumn, ' ', '') AS TextWithoutSpacesFROM ExampleTable;

Detailed Example

Let’s assume you have the following table and data:

  CREATE TABLE ExampleTable (    ID INT,    TextColumn NVARCHAR(100));INSERT INTO ExampleTable (ID, TextColumn)VALUES (1, 'Hello World'),(2, ' SQL Server'),(3, 'Remove all spaces');

To remove all spaces from the TextColumn, you would execute:

  SELECT ID, REPLACE(TextColumn, ' ', '') AS TextWithoutSpacesFROM ExampleTable;The result will be:

ID TextWithoutSpaces

  1 HelloWorld2 SQLServer3 RemoveallspacesUsing in an Update Statement

If you want to update the column in the table to remove all spaces, you can use the UPDATE statement:

  UPDATE ExampleTableSET TextColumn = REPLACE(TextColumn, ' ', '');

After running the update statement, the table ExampleTable will have all spaces removed from the TextColumn.

Putting It All Together

  -- Create tableCREATE TABLE ExampleTable (    ID INT,    TextColumn NVARCHAR(100));-- Insert dataINSERT INTO ExampleTable (ID, TextColumn)VALUES (1, 'Hello World'),(2, ' SQL Server'),(3, 'Remove all spaces');-- Select without spacesSELECT ID, REPLACE(TextColumn, ' ', '') AS TextWithoutSpacesFROM ExampleTable;-- Update table to remove spacesUPDATE ExampleTableSET TextColumn = REPLACE(TextColumn, ' ', '');-- Verify the updateSELECT * FROM ExampleTable;

By following these steps, you can effectively remove all spaces from a string in SQL Server.

4 Months