Copy codeThe Code is as follows:
Declare @ t varchar (255), @ c varchar (255)
Declare table_cursor cursor for select a. name, B. name
From sysobjects a, syscolumns B, policypes c
Where a. id = B. id and a. xtype = 'U' and c. name
In ('Char ', 'nchar', 'nvarchar ', 'varchar', 'text', 'ntext'/* -- if your text (ntext) the type cannot exceed the length of 8000 (4000 */)
Declare @ str varchar (500), @ str2 varchar (500)
Set @ str = 'A'/* here is the character you want to replace */
Set @ str2 = ''/* character after replacement */
Open table_cursor
Fetch next from table_cursor
Into @ t, @ c while (@ fetch_status = 0)
Begin exec ('Update ['+ @ t +'] set ['+ @ c +'] = replace (cast (['+ @ c +'] as varchar (8000) ), ''' + @ str + ''', ''' + @ str2 + ''')')
Fetch next from table_cursor
Into @ t, @ c end close table_cursor deallocate table_cursor;
Processing less than 8000
Update buyok_Orderlist set Notes = replace (cast (Notes as varchar (8000), 'A ','')
If text/ntext exceeds 8000/4000, see the following example <not tried>
Declare @ pos int
Declare @ len int
Declare @ str nvarchar (4000)
Declare @ des nvarchar (4000)
Declare @ count int
Set @ des = '<requested_amount + 1>' -- value to be replaced
Set @ len = len (@ des)
Set @ str = '<requested_amount>' -- the character to be replaced
Set @ count = 0 -- count.
WHILE 1 = 1
BEGIN
Select @ pos = patINDEX ('%' + @ des + '%', propxmldata)-1
From table name
Where condition
IF @ pos> = 0
Begin
DECLARE @ ptrval binary (16)
SELECT @ ptrval = TEXTPTR (field name)
From table name
Where condition
UPDATETEXT table name. Field name @ ptrval @ pos @ len @ str
Set @ count = @ count + 1
End
ELSE
Break;
END
Select @ count