日期:2014-05-18 浏览次数:20577 次
select substring('xie[谢]',charindex('[','xie[谢]')+1,charindex(']','xie[谢]')- charindex('[','xie[谢]')-1)
go if OBJECT_ID('fun_tracy')is not null drop function fun_tracy go create function fun_tracy (@col varchar(20)) returns varchar as begin declare @str varchar(10) select @str=substring(@col,charindex('[',@col)+1,charindex(']',@col)- charindex('[',@col)-1) from tbl1 return @str end
------解决方案--------------------
写个存储过程可以吧
------解决方案--------------------
CREATE PROCEDURE protest @str varchar(100), @RESULT varchar(1000) output AS SET @RESULT=substring(@str,charindex('[',@str)+1,charindex(']',@str)-charindex('[',@str)-1) GO
------解决方案--------------------
--如果诸如xie[谢]之类的是个字段,在表中。 create table tb(col varchar(50)) insert into tb values('xie[谢]') insert into tb values('xie[谢谢]') go --直接使用substring + charindex完成 select col , new_col = (substring(col , charindex('[' , col) + 1 , charindex(']' , col) - charindex('[' , col) - 1)) from tb where charindex('[' , col) < charindex(']' , col) /* col new_col -------------------------------------------------- -------------------------------------------------- xie[谢] 谢 xie[谢谢] 谢谢 (所影响的行数为 2 行) */ --如果你要用函数完成 go create function dbo.f_str(@col varchar(50)) returns varchar(50) as begin declare @str varchar(50) if charindex('[' , @col) < charindex(']' , @col) set @str = (substring(@col , charindex('[' , @col) + 1 , charindex(']' , @col) - charindex('[' , @col) - 1)) else set @str = '' return @str end go --调用函数 select col , new_col = dbo.f_str(col) from tb /* col new_col -------------------------------------------------- -------------------------------------------------- xie[谢] 谢 xie[谢谢] 谢谢 (所影响的行数为 2 行) */ drop function dbo.f_str drop table tb