drop function [dbo].[g_value]
create function [dbo].[g_value]
(
@c varchar(2000),--需要分割的字符串'isnull(姓名,'''') like ''%hao%'' and isnull(日期,'''') like ''%2019-02-06%''','and'
@split varchar(20)--分隔符（例如 , | $）
)
returns @t table(col varchar(200), value varchar(200), 运算符 varchar(30))--返回表
as
begin
declare @t table(col varchar(200), value varchar(200), 运算符 varchar(30));--返回表
declare @c varchar(200),
@split varchar(30)
set @c = 'isnull(姓名,'''') like ''%hao%'' and isnull(日期,'''') like ''%2019-02-06%'''
set @split = 'and'
declare @value varchar(200),
@运算符 varchar(30),
@col varchar(200),
@temp varchar(200)
while(charindex(@split,@c)<>0)
begin
set @temp = substring(@c,1,charindex(@split,@c)-1)
set @c = stuff(@c,1,charindex(@split,@c) + LEN(@split) - 1,'')
--去掉开头空字符
--LTRIM(@temp)
--先去isnull
print @temp
print @c
set @col = substring(@temp,charindex('(',@temp) + 1,charindex(',',@temp)-1)
set @temp = stuff(@temp,1,charindex(') ',@temp) + 1,'')
print @temp
print @col
--LTRIM(@temp)
set @运算符 = substring(@temp,1,charindex(' ',@temp)-1)
set @temp = stuff(@temp,1,charindex('''',@temp),'')
set @value = substring(@temp,1,charindex('''',@temp)-1)
insert @t(col,value,运算符) values (@col,@value,@运算符)
end
set @temp = @c
--去掉开头空字符
--LTRIM(@temp)
--先去isnull
set @col = substring(@temp,charindex('(',@temp) + 1,charindex(',',@temp)-1)
set @temp = stuff(@temp,1,charindex(') ',@temp) + 1,'')
--LTRIM(@temp)
set @运算符 = substring(@temp,1,charindex(' ',@temp)-1)
set @temp = stuff(@temp,1,charindex('''',@temp),'')
set @value = substring(@temp,1,charindex('''',@temp)-1)
insert @t(col,value,运算符) values (@col,@value,@运算符)
select * from @t
return
end