--> Test Data: [T1] if object_id('[T1]') is not null drop table [T1] create table [T1] ([编号] int,[内容] varchar(5)) insert into [T1] select 1,'asdf' union all select 1,'ewrer' union all select 2,'12123' union all select 2,'weew' if object_id('[T2]') is not null drop table [T2] create table [T2] ([编号] int,[内容2] varchar(5)) insert into [T2] select 1,'abbb' union all select 1,'脸' union all select 2,'人人' union all select 2,'weew' --Code --创建临时表 create table #tmp(所在的表及字段 varchar(100),字段内容 text) go create proc proc_search @str varchar(100) as begin--declare @str varchar(100) --set @str='aa' --要搜索的字符串 declare @s varchar(8000) declare tb cursor local for select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'') insert into #tmp select ''['+b.name+'].['+a.name+']'',['+a.name+'] from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'' ' from syscolumns a join sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xusertype in(175,239,231,167) open tb fetch next from tb into @s while @@fetch_status=0 begin exec(@s) fetch next from tb into @s end close tb deallocate tbselect * from #tmp end go --执行exec proc_search 'a' --Drop drop table T1 drop table T2 drop table #tmp drop proc proc_search --Result /* 所在的表及字段 字段内容 ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [T1].[内容] asdf [T2].[内容2] abbb */
string sql="select xx from table where xx='"+xxx+"'" 执行以上语句得到一个string str1 然后用符号的正则表达式分拆这个字段。 string[] Array1=str1.split(","); int a=Array1.length; string str2="" for (int i=0,i<a,i++) { if (Array1[i].IndexOf('"+xxx+"')>-1 ) { str2= Array1[i]; }}
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) --实现split功能 的函数 --date :2003-10-14 as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) return end返回的是一个table,所以执行要用如下格式:select * from dbo.f_split('ABC:BC:C:D:E',':')SQL 中是否包含字符用charindex()
自定义取出第几个分割字符前的字符串,默认位置(0) 格式:dbo.split(字段名,'分隔字符',取出的第几个字符串) 如果没有分隔的字符,则返回整个字符串。 如果取出的位置字符串的位置超出Index则返回空。CREATE FUNCTION [dbo].[split] (@str nvarchar(4000),@code varchar(10),@no int ) RETURNS varchar(200) AS BEGIN declare @intLen int declare @count int declare @indexb int declare @indexe int set @intLen=len(@code) set @count=0 set @indexb=1 if @no=0 if charindex(@code,@str,@indexb)<>0 return left(@str,charindex(@code,@str,@indexb)-1) else return @strwhile charindex(@code,@str,@indexb)<>0 begin set @count=@count+1 if @count=@no break set @indexb=@intLen+charindex(@code,@str,@indexb) end if @count=@no begin set @indexe=@intLen+charindex(@code,@str,@indexb) if charindex(@code,@str,@indexe)<>0 return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code)) else return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1) endreturn ''ENDCREATE FUNCTION [dbo].[split] (@str nvarchar(4000),@code varchar(10),@no int ) RETURNS varchar(200) AS BEGIN declare @intLen int declare @count int declare @indexb int declare @indexe int set @intLen=len(@code) set @count=0 set @indexb=1 if @no=0 if charindex(@code,@str,@indexb)<>0 return left(@str,charindex(@code,@str,@indexb)-1) else return @strwhile charindex(@code,@str,@indexb)<>0 begin set @count=@count+1 if @count=@no break set @indexb=@intLen+charindex(@code,@str,@indexb) end if @count=@no begin set @indexe=@intLen+charindex(@code,@str,@indexb) if charindex(@code,@str,@indexe)<>0 return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code)) else return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1) endreturn ''END --------------------------------- CREATE Function f_trimstr(@str varchar(100))returns varchar(100)--功能:去掉字符串中的所有空格ASbegindeclare @i int declare @s1 varchar(50) declare @result varchar(100) declare @len intselect @result = '' select @str = ltrim(rtrim(@str)) select @len = len(@str) select @i = 1while @i<=@len begin select @s1 = substring(@str,@i,1) if(@s1<>'') begin select @result = @result + @s1 end select @i = @i + 1 endreturn @resultend
if object_id('[T1]') is not null drop table [T1]
create table [T1] ([编号] int,[内容] varchar(5))
insert into [T1]
select 1,'asdf' union all
select 1,'ewrer' union all
select 2,'12123' union all
select 2,'weew'
if object_id('[T2]') is not null drop table [T2]
create table [T2] ([编号] int,[内容2] varchar(5))
insert into [T2]
select 1,'abbb' union all
select 1,'脸' union all
select 2,'人人' union all
select 2,'weew'
--Code
--创建临时表
create table #tmp(所在的表及字段 varchar(100),字段内容 text)
go
create proc proc_search
@str varchar(100)
as
begin--declare @str varchar(100)
--set @str='aa' --要搜索的字符串
declare @s varchar(8000)
declare tb cursor local for
select s='if exists(select 1 from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%'')
insert into #tmp select ''['+b.name+'].['+a.name+']'',['+a.name+'] from ['+b.name+'] where ['+a.name+'] like ''%'+@str+'%''
'
from syscolumns a join sysobjects b on a.id=b.id
where b.xtype='U' and a.status>=0
and a.xusertype in(175,239,231,167)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tbselect * from #tmp
end
go
--执行exec proc_search 'a'
--Drop
drop table T1
drop table T2
drop table #tmp
drop proc proc_search
--Result
/*
所在的表及字段 字段内容
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[T1].[内容] asdf
[T2].[内容2] abbb
*/
string sql="select xx from table where xx='"+xxx+"'"
执行以上语句得到一个string str1
然后用符号的正则表达式分拆这个字段。
string[] Array1=str1.split(",");
int a=Array1.length;
string str2=""
for (int i=0,i<a,i++)
{
if (Array1[i].IndexOf('"+xxx+"')>-1 )
{
str2= Array1[i];
}}
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2003-10-14
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end返回的是一个table,所以执行要用如下格式:select * from dbo.f_split('ABC:BC:C:D:E',':')SQL 中是否包含字符用charindex()
自定义取出第几个分割字符前的字符串,默认位置(0)
格式:dbo.split(字段名,'分隔字符',取出的第几个字符串)
如果没有分隔的字符,则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。CREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @strwhile charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end
if @count=@no
begin set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1) endreturn ''ENDCREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @strwhile charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end
if @count=@no
begin set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1) endreturn ''END
---------------------------------
CREATE Function f_trimstr(@str varchar(100))returns varchar(100)--功能:去掉字符串中的所有空格ASbegindeclare @i int
declare @s1 varchar(50)
declare @result varchar(100)
declare @len intselect @result = ''
select @str = ltrim(rtrim(@str))
select @len = len(@str)
select @i = 1while @i<=@len
begin
select @s1 = substring(@str,@i,1)
if(@s1<>'')
begin
select @result = @result + @s1
end
select @i = @i + 1
endreturn @resultend