Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL new lines following Export/Import procedure

Posted on 2011-03-18
5
Medium Priority
?
285 Views
Last Modified: 2012-05-11
Dear All,
    I'm in the process of moving one database into a new system and I'm encountering the following issue.

In the original database I have a field Message which is an ntext the line spacings are visible when hovering over the field even though the field looks as as a single line. When importing to the new database and into a different system I need to replace the new line identifier with </p><p> and place an <p> in front of the message and a </p> at the end thus if the message looks like this

Helllo, this is the first line and this is the second.

it should be converted to

<p>Hello,</p><p> this is the first line</p><p>and this is the second</p>

any ideas ?

regards
 Original Database view
0
Comment
Question by:Biju708
[X]
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
  • 4
5 Comments
 
LVL 6

Accepted Solution

by:
dan_mason earned 2000 total points
ID: 35164749
Yes, what you need to do is find out exactly what characters are coming in that are causing that line break. When I was facing a similar problem I put together a script that will show you the ASCII values for every character in a string. If you run it against the field in question you'll be able to see where the carriage returns are happening, and the characters involved (it can vary, depending on the original source).

Run the script below to set up the function, then you would execute it like this:

DECLARE @input varchar(max)
SELECT @input=YourField FROM YourTable

SELECT * FROM dbo.tbCharToAscii(@input)

The result will probably be that for a new line you see the figures 13 and 10, in that order, but it might just be one of them or something else.

Anyway, assuming it was 13 followed by 10, you'd then manipulate your string like this:

SET YourField = '<p>'+REPLACE(YourField,CHAR(13)+CHAR(10),'</p><p>')+'</p>'






CREATE FUNCTION [dbo].[tbCharToAscii](@value nvarchar(max))
RETURNS @oT TABLE (pos int PRIMARY KEY,character nchar(1),code varchar(4))
AS
BEGIN

DECLARE @n smallint
SELECT @n=DATALENGTH(@value)/2

WHILE @n>0
	BEGIN
		INSERT @oT (pos,Character,Code)
		SELECT @n,SUBSTRING(@value,@n,1), ASCII(SUBSTRING(@value,@n,1))
		SELECT @n=@n-1
	END

RETURN
END

Open in new window

0
 

Author Comment

by:Biju708
ID: 35164957
Thanks I have some errors on your script though. Can you please have a look maybe I have something wrong.
CREATE FUNCTION [dbo].[tbCharToAscii](@value nvarchar(max))
RETURNS @oT TABLE (pos int PRIMARY KEY,character nchar(1),code varchar(4))
AS
BEGIN

DECLARE @n smallint
SELECT @n=DATALENGTH(@value)/2

WHILE @n>0
	BEGIN
		INSERT @oT (pos,Character,Code)
		SELECT @n,SUBSTRING(@value,@n,1), ASCII(SUBSTRING(@value,@n,1))
		SELECT @n=@n-1
	END

RETURN
END

DECLARE @input varchar(max)
SELECT @input=R_MESSAGE FROM [Database001].[dbo].[PORTAL_REPLY]
SELECT * FROM dbo.tbCharToAscii(@input)

Open in new window

0
 

Author Comment

by:Biju708
ID: 35164963
with errors::

Msg 156, Level 15, State 1, Procedure tbCharToAscii, Line 19
Incorrect syntax near the keyword 'DECLARE'.
0
 

Author Comment

by:Biju708
ID: 35232561
Dear Dan,
   thanks for your input I have worked a little bit on your script and identified where the problem was it's now working like a charm for me it was just a question of only using Char(10) and not CHAR(13)+CHAR(10)

thanks
0
 

Author Closing Comment

by:Biju708
ID: 35232571
I just needed to change to Char(10) only
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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