Search code examples
sqlsql-servertrim

Delete spaces and special characters

This question is linked to :concatenate a column on one line depends on a id

I used this query (thanks Mihai) : 

SELECT id, date,MAX(docline),
Ids=Stuff((SELECT ' ' + doctext  FROM documentation  d WHERE d.id=documentation.id
 FOR XML PATH (''))
             , 1, 1, '' )
 from documentation where date in (02/14/2017)
GROUP BY id,date

I received this line : 

Backup  :                           
D:\Bas.bac                                 
test testtest
tesdttest testtest

I would like to have something like : 

Backup  : D:\Bas.bac test testtest tesdttest testtest

So no space and no special characters like ('
')

Like you can see I have some special characters and too much space... I tried to use RTrim() and LTRIM like : 

SELECT id, date,MAX(docline),
Ids=Stuff(RTRIM(SELECT ' ' + doctext  FROM documentation  d WHERE d.id=documentation.id
 FOR XML PATH ('')))
             , 1, 0, '' )
 from documentation where date in (02/14/2017)
GROUP BY id,date

But I don't see any difference and I have the same behavior.

Thanks,

EDIT : I tried too (without success) :

Stuff((SELECT ' ' + LTRIM(RTRIM(doctext))  FROM documentation  d WHERE d.id=documentation.id FOR XML PATH ('')),1,0,'')
Solution

  • If you just need to remove multiple spaces you could try 

    REPLACE (<string>,'  ', '')

    But in your case it also remove double space between Backup and :