Removing carriage returns from text field 1

[tabbytab]

I've searched archives and found some good stuff.

However when I run on my table (field in ntext)

update <tablename>
set <field> = replace(cast(field as nvarchar),char(13)+char(10),'xyz')

I seem to randomly(?) chop my text field .

Any pointers

Many thanks in advance
TabbyTab

 

[travisbrown]

I've never had much luck with textfields and replace. There was a time that I had to replace a domain name in a content management system. The easiest way for me was to write a replace script in vbs and run it as an asp page. Was slow, but worked on the whole field. Like you, Most I could replace with t-sql was the first 8000 characters or something.

Looked something like this:

SELECT id,textfield FROM tbl

Begin Loop

string = REPLACE(rs(textfield), char(13)+char(10),"xyz")
UPDATE tbl SET textfield = '" & string & '" WHERE id = rs(id)

End Loop

 

[tabbytab]

I now realise that the "chopping" of my text is not random

The length of the text field after the update is either 34 or 32 characters.

So text field

Good at focussing on what needs to get done. Getting to results. Motivate others to achieve our targets.

When I run

update <tablename>
set <field> = replace(cast(field as nvarchar),char(65),'xxx')

I've switched from chr(13)+chr(10) to chr(65) to try and debug problem

So the result of the above is

Good xxxt focussing on whxxxt need

and that's it. What happended to the rest of the field?

TabbyTab but at the moment

 

[bborissov]

What happens if you do:

update <tablename>
set <field> = replace(cast(field as nvarchar(4000)),char(65),'xxx')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[tabbytab]

Cool!

That fixed it.

Many thanks

Borislav

 

[bborissov]

Be carefult though. Your Text field can have more than 4000 bytes in it. You could use varchar(8000) instead of nvarchar(4000)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[travisbrown]

Be carefult though. Your Text field can have more than 4000 bytes in it. You could use varchar(8000) instead of nvarchar(4000)"

That's what I was getting at. I thought the byte limitation was the cause of your truncation. Processing the string outside SQL server (e.g. with vb or vbs) made sure I caught all instances > 8000. Someone might have a preferable SQL solution.

 

[SQLBill]

Per the BOL, default length for NVARCHAR is 30. This is why you should ALWAYS, ALWAYS, ALWAYS include the length value.

-SQLBill

Posting advice: FAQ481-4875