How to remove all whitespace from string?

user_2493921 0
Viewed 36381

I'm having difficulty with a T-SQL query that joins 2 tables using a character column. I suspect that there are some whitespace differences causing the problem but have not been able to track them down. In order to test this theory I'd like to strip all of the whitespaces from the joining columns and see if that resolves the issue. Unfortunately, I'm stuck on how to remove all whitespaces in a T-SQL string. Here is a simple example showing what I've tried (see the test columns):

select
str,
test1 = replace(str, '\\s+' , ''),
test2 = replace(str, '[\s]*' , '')
from 
(
  values
  (''),
  (' '),
  (' xyz'),
  ('abc '),
  ('hello world')
) d (str);

Is there a way to get this to work in T-SQL?

Clarification: by white space, I mean to strip out ALL of the following:

\s         white space (space, \r, \n, \t, \v, \f)
' '        space
\t         (horizontal) tab
\v         vertical tab
\b         backspace
\r         carriage return
\n         newline
\f         form feed
\u00a0     non-breaking space
tsql sql-server
4 Answers

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
user_24939210

Do you mean this? It removes a whitespace (as in created with a spacebar) character

Replace(str,' ', '')
user_70319000

I would suggest that you remove spaces and tabs (4 spaces):

SELECT REPLACE(REPLACE(str,' ', ''), char(9), '')
user_64266920

Replace(str,' ', '')

worked for me.

Check this out:

select
myString,
test1 = replace(myString, ' ' , ''),
test2 = replace(myString, ' ' , '')
from 
(
  values
  (''),
  (' '),
  (' xyz'),
  ('abc '),
  ('hello world')
) d (myString);

Screenshot of output:

enter image description here

user_84580750
Related