May 25, 2006 at 9:09 am
I'm trying to run the following SQL against my column, Testtbl.Task
I want to replace where the ampersand sign got HTML encoded to
"&", and I want to strip it down to only the ampersand sign and delete the "amp" and the ";", that's why my parameters for the UpdateText are 0 and 8 with no update text value.
It executes, but I still have the HTML encoding for the ampersand sign.
USE WI
GO
EXEC sp_dboption 'WI', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(Task)
FROM Testtbl
WHERE Task like '%amp;%'
UPDATETEXT Testtbl.Task @ptrval 0 8
GO
EXEC sp_dboption 'WI', 'select into/bulkcopy', 'false'
GO
May 25, 2006 at 9:48 am
Sorry...line should have been:
UPDATETEXT Testtbl.Task @ptrval 0 8
...since the start will be at the "amp;" (0), and for each character it's 2 bytes (8), and with saying nothing for the update text, it just deletes the "amp;"...at least that's what books online says, but I'm missing something.
May 25, 2006 at 9:50 am
UPDATE TestTbl
SET Task = REPLACE(Task, '&', '&')
That should do the trick.
N 56°04'39.16"
E 12°55'05.25"
May 25, 2006 at 9:52 am
my bad...should have mentioned it's an ntext column
May 25, 2006 at 6:47 pm
XML is for WEB-services. So, it must be parsed and processed there, not in database server.
If you've got '&' in your table then XML file was not processed properly. Fix the program responsible for XML parsing and don't make SQL Server to do what it not suppose to do.
_____________
Code for TallyGenerator
May 25, 2006 at 10:14 pm
Sergiy is correct, but to fix your issue after the fact....
I see two issues here, and keep in mind I'm only beginning to work with text and ntext fields..
1. ~STOLEN from the help file~
For ntext columns, insert_offset is the number of characters (each ntext character uses 2 bytes).
2. The TEXTPTR returns the location of the text. I think you need to use PATINDEX to find
the starting position of the 'amp;' in the field to pass to the UPDATETEXT command. Since you
are updating multiple records, you'll need to load this info into a temp table and loop through it.
------------------------------------------------------
DECLARE
@I int,
@maxRec int,
@pVal Binary(16),
@pos2 int
SELECT
identity(int, 1, 1) as nDex,
ptrval = TEXTPTR(Task),
pos = patindex('%&%', Task) - 1
INTO #tmp
FROM Testtbl
WHERE Task like '%amp;%'
SELECT @maxRec = count(*) from #tmp
SET @I = 1
WHILE @I <= @maxRec
BEGIN
SELECT
@pVal = ptrVal,
@pos2 = pos
FROM #tmp
WHERE nDex = @I
UPDATETEXT Testtbl.Task @pVal @pos2 4
SET @I = @I + 1
END
------------------------------------------------------
I DONT HAVE A MEANS TO TEST THIS RIGHT NOW, SO I SUGGEST
MAKING A COPY OF YOUR TABLE !
I hope this helps, and I never make a claim that I am 100% *GRIN*
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 26, 2006 at 9:40 am
This example will change all occurrences of "&" to "&" for all rows. Another way is to read blocks of 4000 characters at a time into a local variable using READTEXT, and then execute a Replace(). That method fails if the search string crosses the blocksize boundary. A further modifcation to that method involves using CharIndex to find the first occurrence, then READTEXT 4000 characters, run the Replace() on the local variable, then run UPDATETEXT. Do another Charindex() until the string is not found. That reduces the number of CharIndex() calls that are needed.
However, if the execution time is acceptable, sometimes simpler is better.
(EDIT: This examples assumes a primary key called ID which is of type int. Adjust to your actual PKEY).
---------------------------------------------------------------
-- Setup portion
---------------------------------------------------------------
DROP TABLE TestTbl
GO
CREATE TABLE TestTbl
(
id int IDENTITY(1,1)
, task ntext
)
GO
SET NOCOUNT ON
INSERT TestTbl (task) VALUES ('&plus more stuff')
INSERT TestTbl (task) VALUES ('another row with &and more stuff')
INSERT TestTbl (task) VALUES ('&plus two more & and here &that does it.')
INSERT TestTbl (task) VALUES ('still another row with &')
SET NOCOUNT OFF
SELECT * FROM TestTbl
GO
---------------------------------------------------------------
DECLARE @id int
, @ptrVal Binary(16)
, @cpos int
, @searchFor nvarchar(100)
, @replaceWith nvarchar(100)
, @sLen int
SET @searchFor = '&'
SET @replaceWith = '&'
SET @sLen = Len(@searchFor)
-- Get first matching row
SELECT @id = Min(id)
FROM TestTbl
WHERE Task LIKE '%' + @searchFor + '%'
WHILE @id IS NOT NULL
BEGIN
-- Get next text pointer to process
SELECT @ptrval = TEXTPTR(Task)
FROM TestTbl
WHERE id = @id
AND Task LIKE '%' + @searchFor + '%'
-- Loop to update all occurrences of '&' with '&' within the text
-- of the current row
SELECT @cpos = CharIndex(@searchFor, task) - 1
FROM TestTbl
WHERE id = @id
WHILE @cpos >= 0
BEGIN
UPDATETEXT TestTbl.Task @ptrval @cpos @sLen @replaceWith
SELECT @cpos = CharIndex(@searchFor, task) - 1
FROM TestTbl
WHERE id = @id
END
-- Get next matching row
SELECT @id = Min(id)
FROM TestTbl
WHERE Task LIKE '%' + @searchFor + '%'
AND id > @id
END
PRINT 'Loop Terminated'
SELECT * FROM TestTbl
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply