SET quoted_identifier on
set ansi_warnings on
goCREATE FUNCTION dbo.udf_Txt_CharIndexRev ( @SearchFor varchar(255) -- Sequence to be found
, @SearchIn varchar(8000) -- The string to be searched
) RETURNS int -- Position from the back of the string where
-- @SearchFor is found in @SearchIn
WITH SCHEMABINDINGAS BEGIN DECLARE @Result int
, @StringLen int
, @ReverseIn varchar(8000)
, @ReverseFor varchar(255) SELECT @ReverseIn = REVERSE (@SearchIn)
, @ReverseFor = REVERSE(@SearchFor)
, @StringLen = DATALENGTH(@SearchIn) SELECT @Result = CHARINDEX(@ReverseFor, @ReverseIn)
-- return the position from the front of the string
IF @Result > 0
print '@result='+@result+' @stringlenf='+@stringlen
+' datalengthFor='+ DATALENGTH(@SearchFor)
--上面我想看内部变量。可以设置断点么?
SET @Result = @StringLen - (@Result
+ DATALENGTH(@SearchFor) -2)
-- ENDIF RETURN @Result
ENDGO
GRANT EXEC on dbo.udf_Txt_CharIndexRev to PUBLIC
GO
set ansi_warnings on
goCREATE FUNCTION dbo.udf_Txt_CharIndexRev ( @SearchFor varchar(255) -- Sequence to be found
, @SearchIn varchar(8000) -- The string to be searched
) RETURNS int -- Position from the back of the string where
-- @SearchFor is found in @SearchIn
WITH SCHEMABINDINGAS BEGIN DECLARE @Result int
, @StringLen int
, @ReverseIn varchar(8000)
, @ReverseFor varchar(255) SELECT @ReverseIn = REVERSE (@SearchIn)
, @ReverseFor = REVERSE(@SearchFor)
, @StringLen = DATALENGTH(@SearchIn) SELECT @Result = CHARINDEX(@ReverseFor, @ReverseIn)
-- return the position from the front of the string
IF @Result > 0
print '@result='+@result+' @stringlenf='+@stringlen
+' datalengthFor='+ DATALENGTH(@SearchFor)
--上面我想看内部变量。可以设置断点么?
SET @Result = @StringLen - (@Result
+ DATALENGTH(@SearchFor) -2)
-- ENDIF RETURN @Result
ENDGO
GRANT EXEC on dbo.udf_Txt_CharIndexRev to PUBLIC
GO
你如果需要调试,附值在里边看看,然后看看结果是否符合你的要求
SQL2000里边还没有那么高深的东西呢
create table ta (A int,C int)
insert into ta select 1,180
select * from ta
/*--数据
1 180
*/
create proc ttt
as
begin
declare @C int
set @C=(select C from ta where A=1)
print @C
end
goexec ttt
/*数据
180
*/--可以用print