Solved

how do I remove whitespace from in between text

Posted on 2013-11-01
4
266 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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now