Solved

how to replace carriage return with <BR> for a text data type?

Posted on 2001-09-12
12
2,572 Views
Last Modified: 2007-12-19
This is the question i posted in Javascript forum.
someone suggest me to use SQL to solve the problem.

question listed in:
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=javascript&qid=20181576

to be short, i want to replace all the carriage returns in a note field(text type instead of char(200)) with <BR>). I will use this note in HTML.

something like this i think, of course the subquery and syntax won't work.

DECLARE @position int, @note text

SET @position = 1

SET @note= (select note from advnotes)

WHILE @position <= DATALENGTH(@note)

    BEGIN
      if (ASCII(SUBSTRING(@note, @position, 1))) =char(13)
      replace (?) with '<BR>'
      endif
       
    SET @position = @position + 1

    END

GO

Thanks a lot,


0
Comment
Question by:wj53
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 6

Expert Comment

by:jchopde
Comment Utility
You could just do this instead --

SELECT @note = REPLACE(note, char(13), '<BR>')
FROM advnotes
0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
replace(@s,char(13)+ char(10),'<BR>')
will get rid of the crlf - but I don't think it works on a text field.
0
 

Author Comment

by:wj53
Comment Utility
nigelrivett is right.
it doesn't work on text field.
0
 
LVL 6

Expert Comment

by:jchopde
Comment Utility
yes, won't work with TEXT, look into READTEXT, TEXTPTR and the REPLACE functions then.
0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
You will have to use updatetext which will fit in quite well with the substring you are already doing to find the data.

It would probably be faster if you did a patindex on the whole text though rather than character by character.
0
 

Author Comment

by:wj53
Comment Utility
still don't know how to do that.
follow the example from the SQL help on "updatetext", get the following. any suggestion?
Thanks,


DECLARE @noteVar BINARY(16)
DECLARE @InsertPos INT

SELECT @PtrVar = TEXTPTR(note),
       @InsertPos = (PATINDEX(char(13),note)
from advnotes
??
UPDATETEXT advnotes.note
           @noteVar
           @InsertPos
    WITH
           '<BR>'
GO

 

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 6

Expert Comment

by:jchopde
Comment Utility
This is sample code for ONE row of the advnotes table. You can put this logic in a cursor to update ALL rows. Replace <<Where Clause>> to select a single row from the table --

DECLARE @ptrval binary(16)
DECLARE @len int
DECLARE @notes varchar(8000)
DECLARE @start_pos int, @end_pos int, @off_set int, @del_length int

SELECT @len = DATALENGTH(note)
FROM   advnotes
WHERE  <<Where clause>>

select @start_pos = 1
while @start_pos <= @len
  begin
    select @notes = substring(note, @start_pos, 8000)
    from advnotes
    where ord_no = '7088418604'

    select @del_length = datalength(@notes)

    select @notes = replace(@notes, char(13) + char(10), '<BR>')
    select @ptrval = TEXTPTR(note)
    from advnotes
    where <<Where clause>>

    select @off_set = @start_pos - 1
    UPDATETEXT     advnotes.note
               @ptrval @off_set @del_length @notes

    select @start_pos = @start_pos + 8000
  end

HTH.
0
 
LVL 6

Accepted Solution

by:
jchopde earned 60 total points
Comment Utility
Oops, use this instead (left my "test" where clause in one place !) --

DECLARE @ptrval binary(16)
DECLARE @len int
DECLARE @notes varchar(8000)
DECLARE @start_pos int, @end_pos int, @off_set int, @del_length int

SELECT @len = DATALENGTH(note)
FROM   advnotes
WHERE  <<Where clause>>

select @start_pos = 1
while @start_pos <= @len
  begin
    select @notes = substring(note, @start_pos, 8000)
    from advnotes
    where <<Where clause>>

    select @del_length = datalength(@notes)

    select @notes = replace(@notes, char(13) + char(10), '<BR>')
    select @ptrval = TEXTPTR(note)
    from advnotes
    where <<Where clause>>

    select @off_set = @start_pos - 1
    UPDATETEXT     advnotes.note
               @ptrval @off_set @del_length @notes

    select @start_pos = @start_pos + 8000
  end
0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
I was thinking more like this but the above may be more efficient - depends how many there are.
(in the above you can't work on a substring of 8000 because you are adding characters and will go over 8000 - if you work on 7000 at a time it will allow 500 replacements).
Please don't use a cursor.
Below is a method if you have an ID column - otherwise any unique index will do.

-- code to replace
DECLARE @ptrval binary(16)
DECLARE @start_pos int
declare @br varchar(2)
select @br = '%' + char(13) + char(10) + '%'


select @ptrval = TEXTPTR(note)
from advnotes
where <<Where clause>>

select @start_pos = 1

while @start_pos > 0
begin
   select @start_pos = patindex(@br,note)
   from advnotes
   where <<Where clause>>

   UPDATETEXT     advnotes.note
              @ptrval @start_pos, 2, '<br>'
end

-- code to loop through table
declare @id int, @maxid int
select @id = 0, @maxid = max(id) from advnotes
while @id < @maxid
begin
   select @id = min(id) from advnotes where id > @id

   insert above code here
   where clause is  where id = @id
end


0
 
LVL 6

Expert Comment

by:jchopde
Comment Utility
good point, did not think about the replacing 2 with 4 characters "gotcha" !
0
 

Author Comment

by:wj53
Comment Utility
Yes, after i add  "with log" in the UPDATETEXT, it updated the table.
I just realized that i can't update the note field, because other people will use the field in FoxPro too. <BR> would mean nothing in there.

so I only delete
      "UPDATETEXT     advnotes.note
       @ptrval @off_set @del_length @notes"
and add
"select @notes as note" in the end of your script.

I tested it again. It works great in IE. now i have to go back to let it work in Netscape.

Once again, thanks jchopde & nigelrivett . :-)



0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
Your question in Community Support states that you have awarded all your open questions, but these were not completed.  Please accept the expert comment which helped you to then grade and close it.  If you need help in this regard, let us know.  If you want us to split points, we can help you.

Here is how point splits are done:

Let us know which expert you intend to award in the primary question (this) and the points you wish to grant.  We will then reduce the value of the original question to that value and you accept that expert's comment or Proposed Answer within that question.  Then please do the following for each additional expert you wish to award points:
 
Within that same topic area, post a new question for each expert at the point value you wish to grant.
 
Topic = Points for __expertname__
 
In the comments section please include the Question Link (QID number).
 
It is ideal that you also post this information in the original question, and include the new Question Link so they can quickly and easily find it, and an audit trail is maintained.
 
That's all there is to it.  The experts will then either add comments for you to convert to the Accepted Answer to then grade and close, or will Propose an Answer for you to then accept to grade and close.
 
These links are very helpful on site-related processes and Questions/Answers:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
 
Thank you.
 
Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

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

9 Experts available now in Live!

Get 1:1 Help Now