Home > Database > Mysql Tutorial > sql ntext 替换存储过程

sql ntext 替换存储过程

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-07 14:55:01
Original
1171 people have browsed it

sql server 数据库 ntext 字段 的替换存储过程 sqlserver数据库 ntext字段 的替换存储过程 create procedure replace_ntext @table_name nvarchar(100), --目标表名 @field_name nvarchar(100), --目标字段名 @where_str nvarchar(1000), --该表主键字段名 如

sql server 数据库 ntext 字段 的替换存储过程   sqlserver数据库 ntext字段 的替换存储过程
create procedure replace_ntext          
  @table_name    nvarchar(100),   --目标表名      
  @field_name    nvarchar(100),   --目标字段名      
  @where_str        nvarchar(1000),   --该表主键字段名  如id=1     
  @old    nvarchar(1000),        --需要替换的字符串      
  @new    nvarchar(1000)         --替换后的字符串         
as     
begin  
   
declare @SqlStr nvarchar(4000)    
	set @SqlStr = 'declare @pos int, @len int, @count int  set @count=0  set @len=len('''+@old+''') '
	set @SqlStr = @SqlStr + ' select @pos = patINDEX(''%'+@old+'%'',['+@field_name+']) - 1'+' from ['+@table_name+'] where '+ @where_str
	set @SqlStr = @SqlStr + ' while @pos>0 ' 
	set @SqlStr = @SqlStr + ' begin'   
	set @SqlStr = @SqlStr + ' declare @ptrval binary(16)'      
	set @SqlStr = @SqlStr + ' SELECT  @ptrval  =  TEXTPTR(['+@field_name+'])   from ['+@table_name+'] where '+  @where_str
	set @SqlStr = @SqlStr + ' UPDATETEXT ['+@table_name+'].['+@field_name+'] @ptrval  @pos  @len '''+ @new + ''''
	set @SqlStr = @SqlStr + ' select @pos = patINDEX(''%'+@old+'%'',['+@field_name+']) - 1'+' from ['+@table_name+'] where '+ @where_str
	set @SqlStr = @SqlStr + ' set  @count=@count+1 '
	set @SqlStr = @SqlStr + ' end '
	set @SqlStr = @SqlStr + ' select @count'
      --print @SqlStr
        execute sp_executesql   @SqlStr           
end    



Copy after login
Related labels:
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
sql file
From 1970-01-01 08:00:00
0
0
0
php - Overhead of prepare vs sql?
From 1970-01-01 08:00:00
0
0
0
Print sql statement
From 1970-01-01 08:00:00
0
0
0
Pass array to SQL insert query using PHP
From 1970-01-01 08:00:00
0
0
0
sql optimization or
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template