July 7, 2009 at 3:02 pm
I have a text field that has {@CR} inserted into the paragraphs where the Carriage returns were. The script below will remove them all but I have to execute the script for each {@CR}. Is there a way I can have it loop through the paragraph and replace them all with only executing this script once?
Many Regards,
-- Variables to hold cursor data
DECLARE @otxt VARCHAR(1000)
DECLARE @ntxt VARCHAR(1000)
DECLARE @txtlen INT
DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @id INT
SET @otxt = '{@CR}' -- Text to replace
SET @ntxt = ' ' -- Text to insert
SET @txtlen = LEN(@otxt) -- Text length
-- Select Remarks field
DECLARE TextReplaceCursor CURSOR FOR
SELECT ID, TEXTPTR(Remarks), CHARINDEX(@otxt,Remarks)-1 FROM [Contract] WHERE Remarks LIKE '%' + @otxt +'%'
-- Open cursor
OPEN TextReplaceCursor
FETCH NEXT FROM TextReplaceCursor INTO @id, @ptr, @pos
-- Update text field and replace bad text
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Text found in row id = ' + CAST(@id AS VARCHAR) + ' at pos = ' + CAST(@pos AS VARCHAR)
UPDATETEXT [Contract].Remarks @ptr @pos @txtlen @ntxt
FETCH NEXT FROM TextReplaceCursor INTO @id, @ptr, @pos
END
-- Close and deallocate cursor
CLOSE TextReplaceCursor
DEALLOCATE TextReplaceCursor
Drew
July 7, 2009 at 6:05 pm
drewsx2 (7/7/2009)
I have a text field that has {@CR} inserted into the paragraphs where the Carriage returns were. The script below will remove them all but I have to execute the script for each {@CR}. Is there a way I can have it loop through the paragraph and replace them all with only executing this script once?Many Regards,
-- Variables to hold cursor data
DECLARE @otxt VARCHAR(1000)
DECLARE @ntxt VARCHAR(1000)
DECLARE @txtlen INT
DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @id INT
SET @otxt = '{@CR}' -- Text to replace
SET @ntxt = ' ' -- Text to insert
SET @txtlen = LEN(@otxt) -- Text length
-- Select Remarks field
DECLARE TextReplaceCursor CURSOR FOR
SELECT ID, TEXTPTR(Remarks), CHARINDEX(@otxt,Remarks)-1 FROM [Contract] WHERE Remarks LIKE '%' + @otxt +'%'
-- Open cursor
OPEN TextReplaceCursor
FETCH NEXT FROM TextReplaceCursor INTO @id, @ptr, @pos
-- Update text field and replace bad text
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Text found in row id = ' + CAST(@id AS VARCHAR) + ' at pos = ' + CAST(@pos AS VARCHAR)
UPDATETEXT [Contract].Remarks @ptr @pos @txtlen @ntxt
FETCH NEXT FROM TextReplaceCursor INTO @id, @ptr, @pos
END
-- Close and deallocate cursor
CLOSE TextReplaceCursor
DEALLOCATE TextReplaceCursor
I think this might do it for you.
-- create a table to hold sample data
-- if you would have supplied this, I think several others would have jumped in to work on this
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (
RowID int identity,
Remarks text)
-- put some sample data into the table
insert into #tmp
select '2hrobnvmonviuenwiof{@CR}' UNION ALL
select 'qjht890vnovqoifnq92fogbwio;fnqov{@CR}' UNION ALL
select 'q94rthbnfvufntgjnbqfbgfghjigbf{@CR}' UNION ALL
select 'jhtblevniuf3qgmpogfj2ufhbmpfg98gp3vmwog4g{@CR}' UNION ALL
select 'q3jrt3hg4nbn4fi23gr1jtjhifiugfuq3fgn{@CR}'
-- show what is in the table
select * from #tmp
-- update the field, replacing all '{@CR}' in the Remarks field with '!!!!!'
update #tmp
set Remarks = replace(convert(varchar(max), Remarks), '{@CR}', '!!!!!')
where Remarks like '%{@CR}%'
-- show what is now in the table
select * from #tmp
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 8, 2009 at 6:29 pm
Drew,
I am actually in the process of writing a book on the subject and thought I'd ask... why did you think you needed a cursor for this problem? I'm not asking to be a smart guy or anything... I'd really like to know because it may help me help others in avoiding cursors in the future if I understand some of the "Why's". Thanks for any input.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2009 at 9:15 pm
Hi Jeff,
Since the REPLACE function doesn't work on text data types, I think the reason for the cursor was that he was using the UpdateText function. This requires you to call TextPtr on the field, then use that in the UpdateText function. I was having problems figuring out how to do this two-step without a cursor, when I stumbled on converting it to a varchar(max).
However, I also would like to hear back from the OP as to his reason.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2009 at 10:59 am
All,
I actually used the Replace function first but the Text field is much larger than 8000 CHARS so the VARCHAR(MAX) will cut off some of the Data in that field if I use this function:
update #tmp
set Remarks = replace(convert(varchar(max), Remarks), '{@CR}', '!!!!!')
So I need a way to move through the whole Text field. The above works great for small fields.
- Drew
Drew
July 9, 2009 at 11:01 am
All-
So is there a way to use this:
update #tmp
set Remarks = replace(convert(varchar(max), Remarks), '{@CR}', '!!!!!')
and then do something like this:
update #tmp
set Remarks = replace(convert(varchar(max), Remarks), '{@CR}', '!!!!!')
-- Add this to update
WHERE datalength(remarks) < 8000
????
Drew
July 10, 2009 at 9:19 pm
text
Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes.
A varchar(max) can hold the same amount of data as a text field.
I just ran a small test of a varchar(max) variable with 2,000,000 characters in it. Replace did NOT truncate it:
declare @test-2 varchar(max)
set @test-2 = replicate(convert(varchar(max),'*'), 2000000)
print datalength(@test)
print len(@test)
set @test-2 = replace(@test, '*', '%')
print len(@test)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 11, 2009 at 3:53 am
Jeff Moden (7/8/2009)
I am actually in the process of writing a book ...
Give me a hint when it becomes released, please. 🙂
July 11, 2009 at 4:01 am
Sorry, forgot...
Jeff Moden (7/8/2009)
why did you think you needed a cursor for this problem?
I saw many guys using cursors. Most common reason was people have to write SQL statements who primary work with other programming languages. If you work with C#, C++, VB6, ... there is no way to work set based, plus those loops are very fast. A cursor fits to known programming styles, set based solutions are very special for SQL.
Maybe this becomes better with LINQ introduced in .NET 3.0 and new parallelism features in .NET 4.0.
July 12, 2009 at 6:01 am
I'll second that belief that non-database programmers who try to use SQL overuse cursors. I support a vendor provided app, and have spent copious amounts of time beating on the vendor to stop using cursors. Worse they often use nested cursors, so an operation on 900 records takes 45 minutes. Most of their programmers have backgrounds where set based thinking was unknown.
We use cursors occasionally, usually when writing code to create a flat file for an interface or code to process incoming flat files from other systems.
July 12, 2009 at 8:39 pm
Similar situation here to that mentioned above by Ross
I think that one of the main reasons that cursors are used is familiarity with procedural code rather than set-based code - and I suspect that some of the set-based solutions posted by the gurus on this site are above the skill level of many people (myself included) - I see a lot of very cleverly written code here, which often takes me a while to just understand how it works, let alone to actually come up with the code in the first place. Kudos to the gurus 🙂
Practically speaking: often a vendor supplies code containing cursors (sometimes nested) - usually to update some data. I just have to run the code "as is" even if I can see that it is inefficient - to rewrite it would be to assume responsibility for it, rather like voiding the warranty on a new TV if you open it up to fix it.
just my 2c
July 13, 2009 at 12:13 pm
Thanks All!!!
Final script below:
Tested and Works:
UPDATE [Contract]
SET Remarks = REPLACE(CONVERT(VARCHAR(MAX), Remarks), '{@CR}', ' ')
WHERE Remarks LIKE '%{@CR}%' [/color]
Thanks again! YEAH NO CURSOR!
Drew
July 13, 2009 at 12:14 pm
Thanks All!!!
Final script below:
Tested and Works:
UPDATE [Contract]
SET Remarks = REPLACE(CONVERT(VARCHAR(MAX), Remarks), '{@CR}', ' ')
WHERE Remarks LIKE '%{@CR}%' ]
Thanks again! YEAH NO CURSOR!!
Drew
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply