Solved

CHR(127) to CTRL-Enter

Posted on 2007-04-04
3
1,178 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

776 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