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
Solved

CHR(127) to CTRL-Enter

Posted on 2007-04-04
3
1,194 Views
Last Modified: 2012-08-14
I just recently imported some survey type data with linefeeds and caridge returns.
The data was imported into either a memo type column or a nvarchar (2000) type column.
(not my db)

Question...   The CRLF are now represented by square blocks, how can I get them to display as
ALT+ENTER entries?

Perhaps this will help...

Here are two screen shots of what I'm trying to do.
1st: http://www.thebooths.org/1.JPG -> this is how the data loaded
2nd: http://www.thebooths.org/2.JPG --> Is how I would like the data to appear - replacing CHR(127) with a CTRL-ENTER manually.
0
Comment
Question by:cory_booth
3 Comments
 
LVL 6

Expert Comment

by:DocGyver
ID: 18851342
You can use the REPLACE() function to replace all occurances of one string to another string.


something like
UPDATE mytab
SET MyNVARCol = REPLACE(MyNVARCol, char(127), char(13)+Char(10))


Keep in mind that part of the answer might be to replace the character with what ever your display app needs for doing a line break.  If you were to need to send this to HTML you would want to replace char(127) with "<BR>" for a line break.

Not sure what a memo field.  If it is a blob (TEXT, NTEXT, IMAGE) let me know and I can show you how to do a replace type thing with BLOB fields
0
 

Author Comment

by:cory_booth
ID: 18851368
Memo fields are nvarchar types...  
0
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18851389
First do this:
SELECT REPLACE(YourColumnName,char(127),'')
from YourTableName

If thats what you want and you want to update the data then do this:

UPDATE YourTableName
SET YourColumnName = REPLACE(YourColumnName,char(127),'')
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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