Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how do I remove whitespace from in between text

Posted on 2013-11-01
4
Medium Priority
?
274 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.
Suggested Courses

578 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