Solved

CHR(127) to CTRL-Enter

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now