Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how do I remove whitespace from in between text

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

971 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