Replicating a memo field with a twist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replicating a memo field with a twist

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-04 : 03:10:47

Hi
I have one memo field which has a bunch of text in it.
I want to update another memo field from the first one but i want to divide it into lines of 50 characters and put a carriage return at the end of each line and a string value (Note:) at the beginning of each line
But I am not sure how to go about it. The text in the first field may have line breaks and carriage returns or it may not. So is there a way i can strip out anything like that first
See example below

Memo Field1
The quick brown fox jumped over the lazy dog. And the Dish ran away
with the spoon, the little dog laughed to see such fun and the
Cow fell of the wall

Memo Field2
Note: The quick brown fox jumped over the lazy dog. And
Note: the Dish ran away with the spoon, the little dog l
Note: aughed to see such fun and the Cow fell of the wal
Note: l

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-05 : 08:53:26

Is this possible at all?

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-06 : 00:18:44

I think i would need some kind of loop, but I am not sure how to go about it

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-06 : 05:55:28

Some kind soul has worked this out for me. Although the only thing wrong is that carriage returns are being shown as "#x0D;" (with an ampersand at the start - i keep putting it in the post here and it keeps disappearing)

Is there a way i can remove them from the expression below

UPDATE calllog
    SET calldescnote = (
               SELECT  'Note : ' + SUBSTRING(calllog.calldesc,((NUMBER-1)*50)+1,50) + CHAR(10)+ ''

                              FROM calllog 

                              CROSS APPLY (SELECT NUMBER 

                                                   FROM MASTER.DBO.spt_values 

                                                  WHERE TYPE ='P' AND number > 0

                                                      AND number <= (Datalength(calllog.calldesc)/50+1)) X


                              ORDER BY X.NUMBER

                                    FOR XML PATH('')
                           )
     FROM calllog where callid = '00167996'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-06 : 06:07:29

Isn't this a nicer truncation?
http://weblogs.sqlteam.com/peterl/archive/2009/03/18/Human-readable-string-truncation.aspx

N 56°04'39.26"
E 12°55'05.63"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-06 : 06:46:28

I agree with you Peso its much nicer.
however I do not have the necessary skills to make it fit to my scenario above.

i.e. use your logic to apply to my query. I have had a few goes but keep getting syntax errors

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-09-06 : 15:43:32

This is great , I'm learning loads doing this.
I have managed to get your SQL to work on my situation (see query below) but I have a slight problem.

The original text in the Text field is this...
(note there is a carriage return at the end of the first line)

Utility Dug though Telco Fibre outside the Main POP affecting several sites.
Working through the weekend to try to get it up again. The next four weeks are critical as there will be a lot of resource hungry applications being installed and the new influx of students is expected to be much higher than in previous years. notes end here

My final product (being emailed through a rerport )is producing the following: (you can see the problem where the carriage return is)

fault-summary: Utility Dug though Telco Fibre outside the
fault-summary: Main POP affecting several sites.
Working
fault-summary: through the weekend to try to get it up
fault-summary: again. the next four weeks are critical as
fault-summary: there will be a lot of resourec hungry
fault-summary: application being installed and the new
fault-summary: influx of students is expected to be much
fault-summary: higher than in previous years. notes end
fault-summary: here


DECLARE     @String VARCHAR(MAX), 
            @Size TINYINT,
            @CallDescNote VARCHAR (MAX)

SELECT      @String = (Select CallDesc from Calllog where callid = '00167996'),
            @Size = 45 

;WITH Peso (Part, StartPos, Size) 
AS (
    SELECT  1,
            1,
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, 1, @Size) + ' ', @Size))) AS INT)

UNION ALL 

    SELECT  Part + 1, 
            StartPos + Size + 1, 
            CAST(@Size - CHARINDEX(' ', REVERSE(LEFT(SUBSTRING(@String, StartPos + Size + 1, @Size) + ' ', @Size))) AS INT) 
    FROM    Peso 
    WHERE   StartPos + Size <= DATALENGTH(@String) 
) 

SELECT    @CallDescNote = (Select 
             'fault-summary: '+ SUBSTRING(@String, StartPos, Size)  + Char(13) + Char(10)
FROM        Peso 
ORDER BY    Part
FOR XML PATH(''))


update calllog set calldescnote =  RTRIM(Replace(@calldescnote, '#x0D;', char(13)))
from calllog where callid = '00167996'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources