question

MaheshMadhusanka-3900 avatar image
0 Votes"
MaheshMadhusanka-3900 asked EchoLiu-msft answered

How to Remove additional speces after add Replace function

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,

sql-server-generalsql-server-transact-sqlazure-sql-database
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MaheshMadhusanka-2060,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Do you actually want to replace multiple interior spaces with single ones after your replacements?

0 Votes 0 ·

Hi @MaheshMadhusanka-3900,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

0 Votes 0 ·
JuniorGalvaoMVP avatar image
0 Votes"
JuniorGalvaoMVP answered

Mahesh,

What version of SQL Server are you using?

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


[1]: https://docs.microsoft.com/pt-br/sql/t-sql/functions/trim-transact-sql?WT.mc_id=DP-MVP-38732&view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MaheshMadhusanka-3900 avatar image
0 Votes"
MaheshMadhusanka-3900 answered AnuragSharma-MSFT commented

Hi @JuniorGalvaoMVP 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,

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MaheshMadhusanka-3900, can you please provide couple of sample values where you are using replace function and what is the expected output you want? This would help more.

0 Votes 0 ·
MaheshMadhusanka-3900 avatar image
0 Votes"
MaheshMadhusanka-3900 answered EchoLiu-msft commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried to post an answer.
Unfortunately, I got this error:

Access Denied
You don't have permission to access "http://docs.microsoft.com/answers/answers/84776/post.html" on this server.
Reference #18.d5f0f748.1599105499.39230fff

I tried to attach *.txt file with T-SQL. The same story. Mission impossible.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MaheshMadhusanka-3900,

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

22356-image.png
Another method:

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

22324-image.png

Best Regards
Echo



image.png (2.3 KiB)
image.png (2.1 KiB)
image.png (2.5 KiB)
image.png (2.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered YitzhakKhabinsky-0887 edited

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ErlandSommarskog,

I already helped the OP privately.
Unfortunately, this new forum blocks me from posting T-SQL in the answer.
Blocking both ways: directly embedded as well as via an attachment.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @MaheshMadhusanka-3900,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @MaheshMadhusanka-3900,

Has your problem been solved? if it has been solved,please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best Regards
Echo

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.