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

User · Answer

A functional version  udf  that removes spaces  cr  lf  tabs or configurable   select Common ufn RemoveWhitespace   234   asdf   wefwef 3  x      default  as S   Result   234asdfwefwef3x   alter function Common RemoveWhitespace        pString nvarchar max        pWhitespaceCharsOpt nvarchar max    null -- default  tab  lf  cr  space      returns nvarchar max  as   --------------------------------------------------------------------------------------------------     Purpose    Compress whitespace      Example   select Common ufn RemoveWhitespace   234   asdf   wefwef 3  x      default  as s                -- Result  234asdfwefwef3x      Modified    By          Description     ----------  ----------- --------------------------------------------------------------------     2018 07 24  crokusek    Initial Version    --------------------------------------------------------------------------------------------------    begin         declare           maxLen bigint   1073741823  --  2 31 - 1    2  https   stackoverflow com a 4270085 538763           whitespaceChars nvarchar 30    coalesce               pWhitespaceCharsOpt               char 9    char 10    char 13    char 32     -- tab  lf  cr  space      declare          whitespacePattern nvarchar 30            whitespaceChars                  nonWhitespacePattern nvarchar 30             whitespaceChars                  previousString nvarchar max             while   pString     previousString      begin         set  previousString    pString           declare              whiteIndex int   patindex  whitespacePattern   pString            if   whiteIndex  gt  0          begin                                declare                   whitespaceLength int   nullif patindex  nonWhitespacePattern  substring  pString   whiteIndex   maxLen    0  - 1                               set  pString                    substring  pString  1   whiteIndex - 1                     iif  whiteSpaceLength  gt  0  substring  pString   whiteIndex    whiteSpaceLength   maxLen                end             end             return  pString  end go

User · Answer

It seems that everybody keeps referring to a single REPLACE function  Or even many calls of a REPLACE function  But when you have dynamic output with an unknown number of spaces  it wont work  Anybody that deals with this issue on a regular basis knows that REPLACE will only remove a single space  NOT ALL  as it should  And LTRIM and RTRIM seem to have the same issue  Leave it to Microsoft  Here s a sample output that uses a WHILE Loop to remove ALL CHAR 32  values  space      DECLARE  INPUT VAL  VARCHAR 8000  DECLARE  OUTPUT VAL VARCHAR 8000   SET  INPUT VAL          C               A                           SET  OUTPUT VAL    INPUT VAL WHILE CHARINDEX CHAR 32    OUTPUT VAL   gt  0 BEGIN     SET  OUTPUT VAL   REPLACE  INPUT VAL  CHAR 32       END  PRINT  START      INPUT VAL     END  PRINT  START      OUTPUT VAL     END    Here s the output of the above code   START       C               A                          END START CA END   Now to take it a step further and utilize it in an UPDATE or SELECT statement  change it to a udf   CREATE FUNCTION udf RemoveSpaces   INPUT VAL    VARCHAR 8000   RETURNS VARCHAR 8000  AS  BEGIN  DECLARE  OUTPUT VAL VARCHAR 8000  SET  OUTPUT VAL    INPUT VAL -- ITTERATE THROUGH STRING TO LOOK FOR THE ASCII VALUE OF SPACE  CHAR 32   REPLACE IT WITH BLANK  NOT NULL WHILE CHARINDEX CHAR 32    OUTPUT VAL   gt  0 BEGIN     SET  OUTPUT VAL   REPLACE  INPUT VAL  CHAR 32       END  RETURN  OUTPUT VAL END   Then utilize the function in a SELECT or INSERT statement   UPDATE A SET STATUS REASON CODE   WHATEVER dbo udf RemoveSpaces STATUS REASON CODE  FROM WHATEVER  ACCT INFO A WHERE A SOMEVALUE    SOMEVALUE  INSERT INTO SOMETABLE  STATUS REASON CODE  SELECT WHATEVER dbo udf RemoveSpaces STATUS REASON CODE  FROM WHATEVER  ACCT INFO A WHERE A SOMEVALUE    SOMEVALUE

User · Answer

I had this issue today and replace   trim did the trick  see below    update table foo  set column bar    REPLACE LTRIM RTRIM column bar                before and after     old-bad   column bar        New-fixed    column bar           xyz                             xyz               xyz                             xyz             xyz                             xyz             xyz                             xyz             xyz                             xyz             xyz                             xyz

User · Answer

replace replace column Name CHAR 13      CHAR 10

User · Answer

REPLACE   function   REPLACE field

User · Answer

Try to use like this  if normal spaces are not removed by LTRM or RTRM LTRIM RTRIM REPLACE REPLACE REPLACE Column data  CHAR 9        CHAR 10        CHAR 13

User · Answer

Just in case you need to TRIM spaces in all columns  you could use this script to do it dynamically   --Just change table name declare  MyTable varchar 100  set  MyTable    MyTable   --temp table to get column names and a row id select column name  ROW NUMBER   OVER ORDER BY column name  as id into  tempcols from INFORMATION SCHEMA COLUMNS  WHERE   DATA TYPE IN   varchar    nvarchar   and TABLE NAME    MyTable  declare  tri int select  tri   count    from  tempcols declare  i int select  i   0 declare  trimmer nvarchar max  declare  comma varchar 1  set  comma         --Build Update query select  trimmer    UPDATE  dbo        MyTable      SET    WHILE  i  lt    tri  BEGIN      IF   i    tri          BEGIN         set  comma              END     SELECT   trimmer    trimmer   CHAR 10         COLUMN NAME        LTRIM RTRIM      COLUMN NAME          comma     FROM     tempcols     where id    i      select  i    i 1 END  --execute the entire query EXEC sp executesql  trimmer  drop table  tempcols

User · Answer

if you want to remove spaces -  and another text from string then use following    suppose you have a mobile number in your Table like  718-378-4957  or    7183784957  and you want replace and get the mobile number then use following Text   select replace replace replace replace MobileNo  -                              from EmployeeContactNumber   Result  -- 7183784957

User · Answer

Check and Try the below script  Unit Tested -  --Declaring DECLARE  Tbl TABLE col 1 VARCHAR 100     --Test Samples INSERT INTO  Tbl  col 1  VALUES     EY     y             Salem         EY     P    ort       Chennai            EY     Old           Park            EY            EY             null    d                                f     SELECT col 1 AS INPUT      LTRIM RTRIM      REPLACE      REPLACE      REPLACE      REPLACE      REPLACE          REPLACE          REPLACE          REPLACE          REPLACE          REPLACE          REPLACE col 1 CHAR 10                CHAR 11                CHAR 12                CHAR 13                CHAR 14                CHAR 160                CHAR 13  CHAR 10            CHAR 9                CHAR 17  CHAR 18        CHAR 18  CHAR 17           CHAR 17  CHAR 18              AS  OUTPUT  FROM  Tbl

User · Answer

To remove the spaces in a string left and right  To remove space in middle use Replace   You can use RTRIM   to remove spaces from the right and LTRIM   to remove spaces from the left hence left and right spaces removed as follows   SELECT   FROM table WHERE LTRIM RTRIM username     LTRIM RTRIM  Bob alias baby

User · Answer

100  working  UPDATE table name SET   column name  replace  column name               Remove white space  UPDATE table name SET   column name  replace  column name     n          Remove newline  UPDATE table name SET   column name  replace  column name     t          Remove all tab   You can use  column name  or column name  Thanks  Subroto

User · Answer

this is useful for me   CREATE FUNCTION dbo TRIM  String VARCHAR MAX   RETURNS VARCHAR MAX  BEGIN     RETURN LTRIM RTRIM REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE  String CHAR 10        CHAR 13        char 9        CHAR 32                      CHAR 32      END GO

User · Answer

Just a tip  in case you are having trouble with the replace function  you might have the datatype set to nchar  in which case it is a fixed length and it will not work

User · Answer

I would use a REPLACE  select REPLACE    Hello   How Are You                 REPLACE

User · Answer

For some reason  the replace works only with one string each time  I had a string like this  Test nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp MSP  and I want to leave only one space   I used the approach that  Farhan did  but with some modifications   CREATE FUNCTION ReplaceAll        OriginalString varchar 8000        StringToRemove varchar 20        StringToPutInPlace varchar 20    RETURNS varchar 8000  AS BEGIN declare  ResultStr varchar 8000  set  ResultStr    OriginalString while charindex  StringToRemove   ResultStr   gt  0     set  ResultStr   replace  ResultStr   StringToRemove   StringToPutInPlace   return  ResultStr END   Then I run my update like this  UPDATE tbTest SET Description   dbo ReplaceAll Description             WHERE ID   14225   Then I got this result  Test MSP  Posting here if in case someone needs it as I did   Running on  Microsoft SQL Server 2016  SP2

User · Answer

t-sql replace http   msdn microsoft com en-us library ms186862 aspx  replace val

User · Answer

To make all of the answers above complete  there are additional posts on StackOverflow on how to deal with ALL whitespace characters  see https   en wikipedia org wiki Whitespace character for a full list of these characters     TSQL 2008 Using LTrim RTrim and still have spaces in the data How do I remove non-breaking spaces from a column in SQL server  What  39 s a good way to trim all whitespace characters from a string in T-SQL without UDF and without CLR

User · Answer

If it is an update on a table all you have to do is run this update multiple times until it is affecting 0 rows   update tableName set colName   REPLACE LTRIM RTRIM colName               where colName like

User · Answer

If there are multiple white spaces in a string  then replace may not work correctly  For that  the following function should be used   CREATE FUNCTION RemoveAllSpaces        InputStr varchar 8000    RETURNS varchar 8000  AS BEGIN declare  ResultStr varchar 8000  set  ResultStr    InputStr while charindex       ResultStr   gt  0     set  ResultStr   replace  InputStr            return  ResultStr END   Example   select dbo RemoveAllSpaces  aa  aaa       aa aa                 a     Output   aaaaaaaaaa

User · Answer

Reference taken from this blog   First  Create sample table and data   CREATE TABLE tbl RemoveExtraSpaces        Rno INT       Name VARCHAR 100    GO  INSERT INTO tbl RemoveExtraSpaces VALUES  1  I    am     Anvesh   Patel   INSERT INTO tbl RemoveExtraSpaces VALUES  2  Database   Research and     Development     INSERT INTO tbl RemoveExtraSpaces VALUES  3  Database    Administrator        INSERT INTO tbl RemoveExtraSpaces VALUES  4  Learning    BIGDATA    and       NOSQL    GO   Script to SELECT string without Extra Spaces   SELECT       Rno        Name  AS StringWithSpace      LTRIM RTRIM REPLACE REPLACE REPLACE  Name  CHAR 32                      CHAR 32     AS StringWithoutSpace FROM tbl RemoveExtraSpaces   Result   Rno         StringWithSpace                                 StringWithoutSpace ----------- -----------------------------------------  --------------------------------------------- 1           I    am     Anvesh   Patel                      I am Anvesh Patel 2           Database   Research and     Development         Database Research and Development 3           Database    Administrator                       Database Administrator 4           Learning    BIGDATA    and       NOSQL          Learning BIGDATA and NOSQL

User · Answer

This does the trick of removing the spaces on the strings   UPDATE     tablename SET     columnname   replace columnname

User · Answer

Simply replace it   SELECT REPLACE fld or variable             Edit  Just to clarify  its a global replace  there is no need to trim   or worry about multiple spaces for either char or varchar   create table  t       c char 8       v varchar 8    insert  t  c  v  values        a a        a a              a a        a a                a a        a a              a a        a a      select           c        IN         replace c                  OUT  from  t   union all select           v              replace v                 from  t    Result  IN             OUT                      a a            aa   a a            aa     a a          aa     a a          aa   a a            aa   a a            aa     a a          aa     a a          aa

User · Answer

Syntax for replacing a specific characters   REPLACE   string expression   string pattern   string replacement       For example in the string  HelloReplaceThingsGoing  Replace word is replaced by How  SELECT REPLACE  HelloReplaceThingsGoing   Replace   How    GO

[sql] Remove all spaces from a string in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to string

Examples related to whitespace

Examples related to trim