• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1232
  • Last Modified:

CHR(127) to CTRL-Enter

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.
1 Solution
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
cory_boothAuthor Commented:
Memo fields are nvarchar types...  
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),'')
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now