Solved

how do I remove whitespace from in between text

Posted on 2013-11-01
4
268 Views
Last Modified: 2013-11-07
I have text in a field that looks like the following:

Contract_no
-----------------
AB-123C-4567
DE-892F-
6007
GH-292I-J2006
KL-3434-
M2008

That is actually 4 rows of data. The data was entered for some of the records with what looks like a hard return. When I try to use BCP to export the records and remove the whitespace it doesn't work. How should I fix this?

I tried the following select statement in my export select RTRIM(LTRIM(contract_no)) from contracts
0
Comment
Question by:sikyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 39616800
where you view the information ?

 Contract_no
-----------------
AB-123C-4567
DE-892F-
6007
GH-292I-J2006
KL-3434-
M2008

You view the data in webpage or in mysql database in phpmyadmin ?

If you view it in html page, you need to
set p tag with CSS

<style>
p
{
white-space:nowrap;
}
</style>

take a look for minus-sign in html page
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/CSS/Q_28230776.html
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39616816
You can try stripping them out of the data using REPLACE.  CHAR(10) and CHAR(13) are newline and carriage return characters.

SELECT REPLACE(REPLACE(myColumn, CHAR(13), ''), CHAR(10), '')
0
 

Author Comment

by:sikyala
ID: 39616828
I mannually entered the sample data I am viewing it inside of sql server I open the table and look at the field values. Then I export it using bcp the data appears in the text file the same way. I will try the replace argument
0
 

Author Closing Comment

by:sikyala
ID: 39630727
thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question