How to Remove additional speces after add Replace function

Question

Hi Team,

Currently i have faced issue as a after add replace function to SQL column to remove comma, then output parsing as a addotional spaces on the SQL column data feilds, According to i have used below trim method but its not worked, Could you please advise on this..

Replace Function- Replace(Replace(Replace(Replace(OA_AdditionalAddressInformation,',',' '),'"',''),'/',' '),'"','') as OA_AdditionalAddressInformation,

After adding trim on it. - LTRIM(RTRIM(Replace(Replace(Replace(Replace(OA_AdditionalAddressInformation,',',' '),'"',''),'/',' '),'"',''))) as OOA_AdditionalAddressInformation,

Answer 1

Mahesh,

What version of SQL Server are you using?

As of version 2017 there is the [TRIM()][1] function.

Answer 2

Hi @Junior Galvão MVP and Team,

Our SQL Server Version is
SQL Server Version: 13.00.5820.21 (x64)
SQL Server 2016

and Could you please Support to add this suggetion trim command to below my Current colum condition.

Current Replace column.

Replace(Replace(Replace(Replace(OA_AdditionalAddressInformation,',',' '),'"',''),'/',' '),'"','') as OA_AdditionalAddressInformation,

Answer 3

Hi Anurag and Team,

Currently we have SQL tables Columns there have additional Commas inside the column and '/' data

(Ex value- MAISDIJK 5,PO. BOX 56, )
according to we want to replace inserted of comma and "/", Below is a current replace condition i used but once i used it its parsing unremovable addional spaces. Could you please support to remove it.

Current Replace column.

Replace(Replace(Replace(Replace(OA_AdditionalAddressInformation,',',' '),'"',''),'/',' '),'"','') as OA_AdditionalAddressInformation,

Thanks
Mashe

Answer 4

Hi @Mahesh Madhusanka ,

You can try char(10), char(13):

 select replace(replace(replace(Replace(Replace(Replace(Replace('Ex value- MAISDIJK 5,PO. BOX 56,',',',' '),'"',''),'/',' '),'"',''),char(10)+char(13),''),char(10),''),char(13),'') as OA_AdditionalAddressInformation  

Another method:

select replace( Replace(Replace(Replace(Replace('Ex value- MAISDIJK 5,PO. BOX 56,' ,',',' '),'"',''),'/',' '),'"','') ,' ','') as OA_AdditionalAddressInformation  

Best Regards
Echo

Answer 5

When there are characters you can't seem to remove, cast the value to binary to see what character codes actually are. For instance, it could be a hard space, char(160). Or as Echo suggests CR-LF.