sql-server

Remove all spaces from a string in SQL Server


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.


Solution

  • 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"