Remove all spaces from a string in SQL Server

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.