How to Remove White Space Characters from a String in SQL Server

Remove all spaces from a string in SQL Server

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"

How to remove all whitespace from string?

This piece of code helped figure out exactly what kind of whitespace was present in the original query that had the join issue:

select distinct
fieldname,
space             = iif(charindex(char(32), fieldname) > 0, 1, 0),
horizontal_tab    = iif(charindex(char(9), fieldname) > 0, 1, 0),
vertical_tab      = iif(charindex(char(11), fieldname) > 0, 1, 0),
backspace         = iif(charindex(char(8), fieldname) > 0, 1, 0),
carriage_return   = iif(charindex(char(13), fieldname) > 0, 1, 0),
newline           = iif(charindex(char(10), fieldname) > 0, 1, 0),
formfeed          = iif(charindex(char(12), fieldname) > 0, 1, 0),
nonbreakingspace  = iif(charindex(char(255), fieldname) > 0, 1, 0)
from tablename;

It turned out there were carriage returns and new line feeds in the data of one of the tables. So using @scsimon's solution this problem was resolved by changing the join to this:

on REPLACE(REPLACE(a.fieldname, CHAR(10), ''), CHAR(13), '') = b.fieldname

Remove ALL white spaces in a string sql server

In addition to using replace, on SQL Server 2017+ to avoid multiple nested functions, you can use translate if there are multiple characters in a string you want to remove:

Remove space, hyphen, slash:

declare @string varchar(50)='12345 67-9\x'

select Replace(Translate(@string, ' -\','???'),'?','')

Removing white spaces and special characters from SQL

Added as a wiki answer in order to retain the comment made by @lptr. Query by @lptr explanation mine (@DaleK).

Your attempt was close, but only worked for single characters... the one that failed was because you had multiple characters that needed replacing and once you remove the white space they are all next to each other and don't match the original string anymore.

This answer cleverly replaces all the letter characters with a "*" using translate as step 1, then using translate again on the original column value, replaces all the non-letter characters with a "*" as step 2, then finally replaces all "*" characters with an empty string.

Note also the use of replication to avoid typing the same character in multiple times.

create table samp(code varchar(50));

insert into samp(code)
values
('N/A'),
('@email'),
('Hot-topic'),
('#sql#%'),
('White paper. ');

select s.code, n.nonletters, l.letters
from samp as s
cross apply (values(translate(s.code, 'abcdefghijklmnopqrstuvwxyz', replicate('*', 26)))) as n (nonletters)
cross apply (values(replace(translate(s.code, n.nonletters, replicate('*', len(n.nonletters+'.')-1)), '*', ''))) as l (letters);

SQL - How to remove a space character from a string

As mentioned by a couple folks, it may not be a space. Grab a copy of ngrams8k and you use it to identify the issue. For example, here we have the text, " SPACE" with a preceding space and trailing CHAR(160) (HTML BR tag). CHAR(160) looks like a space in SSMS but isn't "trimable". For example consider this query:

DECLARE @string VARCHAR(100) = ' SPACE'+CHAR(160);
SELECT '"'+@string+'"'

Using ngrams8k you could do this:

DECLARE @string VARCHAR(100) = ' SPACE'+CHAR(160);

SELECT
  ng.position,
  ng.token,
  asciival = ASCII(ng.token)
FROM   dbo.ngrams8k(@string,1) AS ng;

Returns:

position   token   asciival
---------- ------- -----------
1                  32
2          S       83
3          P       80
4          A       65
5          C       67
6          E       69
7                  160

As you can see, the first character (position 1) is CHAR(32), that's a space. The last character (postion 7) is not a space.
Knowing that CHAR(160) is the issue you could fix it like so:

SET @string = REPLACE(LTRIM(@string),CHAR(160),'')

If you are using SQL Server 2017+ you can also use TRIM which does a whole lot more than just LTRIM-and-RTRIM-ing. For example, this will remove
leading and trailing tabs, spaces, carriage returns, line returns and HTML BR tags.

SET @string = SELECT TRIM(CHAR(32)+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(160) FROM @string)

How to remove white space characters from a string in SQL Server

Using ASCII(RIGHT(ProductAlternateKey, 1)) you can see that the right most character in row 2 is a Line Feed or Ascii Character 10.

This can not be removed using the standard LTrim RTrim functions.

You could however use (REPLACE(ProductAlternateKey, CHAR(10), '')

You may also want to account for carriage returns and tabs. These three (Line feeds, carriage returns and tabs) are the usual culprits and can be removed with the following :

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(ProductAlternateKey, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')))

If you encounter any more "white space" characters that can't be removed with the above then try one or all of the below:

--NULL
Replace([YourString],CHAR(0),'');
--Horizontal Tab
Replace([YourString],CHAR(9),'');
--Line Feed
Replace([YourString],CHAR(10),'');
--Vertical Tab
Replace([YourString],CHAR(11),'');
--Form Feed
Replace([YourString],CHAR(12),'');
--Carriage Return
Replace([YourString],CHAR(13),'');
--Column Break
Replace([YourString],CHAR(14),'');
--Non-breaking space
Replace([YourString],CHAR(160),'');

This list of potential white space characters could be used to create a function such as :

Create Function [dbo].[CleanAndTrimString] 
(@MyString as varchar(Max))
Returns varchar(Max)
As
Begin
    --NULL
    Set @MyString = Replace(@MyString,CHAR(0),'');
    --Horizontal Tab
    Set @MyString = Replace(@MyString,CHAR(9),'');
    --Line Feed
    Set @MyString = Replace(@MyString,CHAR(10),'');
    --Vertical Tab
    Set @MyString = Replace(@MyString,CHAR(11),'');
    --Form Feed
    Set @MyString = Replace(@MyString,CHAR(12),'');
    --Carriage Return
    Set @MyString = Replace(@MyString,CHAR(13),'');
    --Column Break
    Set @MyString = Replace(@MyString,CHAR(14),'');
    --Non-breaking space
    Set @MyString = Replace(@MyString,CHAR(160),'');

    Set @MyString = LTRIM(RTRIM(@MyString));
    Return @MyString
End
Go

Which you could then use as follows:

Select 
    dbo.CleanAndTrimString(ProductAlternateKey) As ProductAlternateKey
from DimProducts

Removing trailing spaces and whitespaces from SQL Server column

Check the below;

1. Find any special characters like char(10), char(13) etc in the field value.
2. Check the status of ANSI_PADDING ON. Refer this MSDN article.

How to remove white spaces from columns in SQL Server 2000?

you can use REPLACE/RTRIM/LTRIM

select RTRIM(LTRIM(column_name)) from table_name

or

select  replace(column_name, ' ', '') from table_name

---

Related Topics