How to eliminate empty space in address column

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

, +

Hi
I have problem in my database.

It is desktop application in VB 6,Database is MSSQL 2014

Our client entered data in address column like below

NO.15, JALAN WELMAN, 
JALAN STORE 
TINGAKT TIGA
RAWANG,

But I need in single line like

NO.15, JALAN WELMAN,JALAN STORE,TINGAKT TIGA,RAWANG,

More then 50000 data in table. Cannot do one by one.

Pls advice me

Maideen

What I have tried:

UPDATE [dbo].[MVendorMaster] SET [ADDRESS]= 	
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE([address],CHAR(32),'()'),')(',''),'()',CHAR(32))))

Posted 26-Apr-17 17:46pm

Maideen Abdul Kader

Updated 26-Apr-17 20:11pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 27-Apr-17 0:53am

Try replacing CHAR(10) and CHAR(13) with COMMAs ',' and then replace ',,' with ',' .

And update your data entry code to fix it before it hits the database.

Maideen Abdul Kader 27-Apr-17 1:28am

Thank you for reply...
Still same. Is there any other solution?
Pls advice me
Thank you

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Accepted Answer · 2017-04-26T20:11:00

Solution 1

You should try doing the following

SQL

UPDATE MVendorMaster SET ADDRESS = REPLACE(REPLACE(REPLACE([ADDRESS], CHAR(13), ''), CHAR(10), ''), CHAR(9), '')

I believe the CHAR values correspond to the following
CHAR(9) is tab
CHAR(13) is carriage return
CHAR(10) is a line feed

Posted 26-Apr-17 20:11pm

David_Wimbley

Comments

PIEBALDconsult 27-Apr-17 9:19am

But I think he wants to replace newline sequences with COMMAs, as in the example he provided.

David_Wimbley 27-Apr-17 13:12pm

Well without access to their database or anything like that I can't really help with which REPLACE as you could potentially end up with something like No. 15,,,, (four commas due to one already being in example above) but I would hope that it is pretty clear to just put ',' instead of '' in the UPDATE statement where necessary.