--**************************************
-- Name: A REPLACE function for a TEXT field as a Stored Procedure
-- Description:This is a Stored Procedure version of my original submission: A REPLACE function for a TEXT field
http://www.pscode.com/vb/scripts/ShowCode.asp?txtCodeId=367&lngWId=5
-- By: J Shortland
--
-- Inputs:@TableName varchar( 255 ) -- Table name containing Blob field
@ColumnName varchar( 255 ) -- Column name of Blob field
@SearchStr varchar( 255 ) -- Search string
@ReplaceStr varchar( 255 ) -- Replacement string
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=395&lngWId=5--for details.--**************************************
create procedure [dbo].[ReplaceBlob] ( @TableName varchar( 255 ), -- Table name containing Blob field
@ColumnName varchar( 255 ), -- Column name of Blob field
@SearchStr varchar( 255 ), -- Search string
@ReplaceStr varchar( 255 ) -- Replacement string
)
as
/*********************************************************************
--
-- Name ReplaceBlob
-- Date Created: 21/01/2002
-- Created By: J Shortland
-- Description: Generically, the Replace function in SQL Server
-- doesn't support replacing a particular string
-- with another within a TEXT field; this can.
--
-- Steps Performed: Step 01 : Construct & assign the static Replace function to the local variable
-- Step 02 : Replace the meta-tag '<TableName>' with the parameter '@TableName'
-- Step 03 : Replace the meta-tag '<ColumnName>' with the parameter '@ColumnName'
-- Step 04 : Replace the meta-tag '<SearchStr>' with the parameter '@SearchStr'
-- Step 05 : Replace the meta-tag '<ReplaceStr>' with the parameter '@ReplaceStr'
-- Step 06 : Display the compiled SQL statement
-- Step 07 : Execute the compiled SQL statement
--
-- History:
-- |-------------------------------------------------------------------|
-- | 21/01/2002 JHS Creation
-- |-------------------------------------------------------------------|
--
-- ********************************************************************/
set nocount on
declare @SQL varchar( 8000 ) -- Stores the SQL statement to be executed
-- Step 01 : Construct & assign the static Replace function to the local variable
set @SQL =
'
/* -- Start of Replace Script -- */
set nocount on
declare @TextPointer varbinary( 16 ) -- Stores the handle for the TEXT field to be processed
declare @DeleteLength int -- Stores the length of string to be removed
declare @OffSet int -- Stores the starting position for the update
-- Step 01 : Obtain the handle for the TEXT field to be processed
select @TextPointer = textptr( <ColumnName> )
from <TableName>
-- Step 02 : Set the delete length of string to be removed from the TEXT field
set @DeleteLength = len( ''<SearchStr>'' )
-- Step 03 : Initialise the starting position for the update
set @OffSet = 0
-- Step 04 : Loop thru table until all instances of the search string are replaced
while ( select count( * )
from <TableName>
where patindex( ''%<SearchStr>%'', <ColumnName> ) <> 0
) > 0
begin
-- Step 04a : Set the starting position for the update
select @OffSet = patindex( ''%<SearchStr>%'', <ColumnName> ) - 1
from <TableName>
where patindex( ''%<SearchStr>%'', <ColumnName> ) <> 0
-- Step 04b : Replace the search string with the replacement string
updatetext <TableName>.<ColumnName> -- table_name.dest_column_name
@TextPointer -- dest_text_ptr
@OffSet -- insert_offset
@DeleteLength -- delete_length
''<ReplaceStr>'' -- inserted_data
end
set nocount off
/* -- End of Replace Script -- */
'
-- Step 02 : Replace the meta-tag '<>' with the parameter '@TableName'
set @SQL = replace( @SQL, '<TableName>', @TableName )
-- Step 03 : Replace the meta-tag '<ColumnName>' with the parameter '@ColumnName'
set @SQL = replace( @SQL, '<ColumnName>', @ColumnName )
-- Step 04 : Replace the meta-tag '<SearchStr>' with the parameter '@SearchStr'
set @SQL = replace( @SQL, '<SearchStr>', @SearchStr )
-- Step 05 : Replace the meta-tag '<ReplaceStr>' with the parameter '@ReplaceStr'
set @SQL = replace( @SQL, '<ReplaceStr>', @ReplaceStr )
-- Step 06 : Display the compiled SQL statement
print @SQL
-- Step 07 : Execute the compiled SQL statement
exec ( @SQL )
set nocount off
|
