July 8, 2008 at 8:41 pm
Hi All,
i am having difficulties to find a function replace some text into and NTEXT field of a SQLServer database.
I need to remove a string that always come at the end of each records and is a fixed lenght, about 20 char.
maybe can be done in 2 ways, a search and replace or a trm of the final part of the string.
Anyone can suggest a piece of code to run?
Thanks!
July 8, 2008 at 9:51 pm
What have you tried so far?
Why can't you change the NText field to NVarchar(MAX)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 9, 2008 at 8:35 am
If you can't replace the field with nvarchar(max), instead of ntext, then you'll probably need to use the TextPointer and WriteText methods. Look up those keywords in Books Online, it'll tell you how to use them. (They're a bit of a pain. Replacing ntext with nvarchar(max) is definitely better, if you can do it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2008 at 2:06 pm
If you are in 2005, you can cast it to nVarChar(max) before running it through the replace. Otherwise you'll have to break it up into 4000 char pieces, replace in each of the pieces, and reassemble it.
Or you can have the application do the replace.
September 1, 2009 at 4:11 pm
I just ran into this problem. I had to replace a line in an email template in our database. I have no idea why the field was an ntext, but it was. Anyway here is what I did:
--First create a temp table to toss our data in
CREATE TABLE #Tempy
(recid INT, body NTEXT)
--next we'll insert and change the data in here and add an ID (if there is one)
INSERT INTO #Tempy (recid, body)
SELECT recid, CAST(
REPLACE(
CAST(body AS nvarchar(max)),
'I want to replace this text',
'with this text'
)
AS ntext)
from dbo.yourtable
-- next I want to select it to check the data and make sure it's ok
SELECT * --or if it's a big table select top 10 recid, body
FROM #Tempy
--last, let's update the original table with the replaced ntext data
UPDATE dbo.yourtable
SET body = #tempy.body
FROM #Tempy
WHERE yourtable.recid = #Tempy.recid
September 18, 2009 at 4:37 am
Hi All,
I have this code trying to replace some string in my ntext field. But when i execute it i've got the error:
Deletion length 10 is not in the range of available text, ntext, or image data.
I've searched the google about this problem but nothing was useful. Any ideas?
DECLARE @FindString VARCHAR(100)
DECLARE @ReplaceString VARCHAR(100)
DECLARE @TextPointer VARBINARY(16)
DECLARE @DeleteLength INT
DECLARE @OffSet INT
SELECT @TextPointer = TEXTPTR([skincss])
FROM [tbl_Skins]
SET @ReplaceString='STEFKA'
SET @FindString = '1234567890'
SET @DeleteLength = LEN(@FindString)
SET @OffSet = 0
SET @FindString = '%1234567890%'
WHILE (SELECT COUNT(*)
FROM [tbl_Skins]
WHERE PATINDEX(@FindString, [skincss]) > 0 and skinid=695) > 0
BEGIN
SELECT @OffSet = PATINDEX(@FindString, [skincss]) - 1
FROM [tbl_Skins]
WHERE PATINDEX(@FindString, [skincss]) > 0 and skinid=695
UPDATETEXT [tbl_Skins].[skincss]
@TextPointer
@OffSet
@DeleteLength
@ReplaceString
END
September 18, 2009 at 7:14 am
*ahem*
RBarryYoung (7/8/2008)
What have you tried so far?Why can't you change the NText field to NVarchar(MAX)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply