DECLARE @contexts VARCHAR(60),@Thresholds VARCHAR(200),@index INT,@context VARCHAR(20),@Threshold VARCHAR(60)
SET @contexts= '0,1,2,3,4,5,6,7'
SET @Thresholds= 'Not in Handoff, Softer,Soft,Softer Soft, Softer Softer, Soft4 Way, Soft5 Way, Soft6 Way'@contexts 中是 值 和 @Thresholds 中是 备注
我现在想 知道 @contexts 中为1 的 对应的re
要写成 函数 来 输出。
请大家赐教。
SET @contexts= '0,1,2,3,4,5,6,7'
SET @Thresholds= 'Not in Handoff, Softer,Soft,Softer Soft, Softer Softer, Soft4 Way, Soft5 Way, Soft6 Way'@contexts 中是 值 和 @Thresholds 中是 备注
我现在想 知道 @contexts 中为1 的 对应的re
要写成 函数 来 输出。
请大家赐教。
解决方案 »
- 想写一个存储过程删除100多张表,每个表要删除几万条记录
- !!!!!distinct引发的问题!!!!!!
- 急求教,在线:sql2000 安装问题。安装程序配置服务器失败。参考服务器错误日志和 C:\WINNT\sqlstp.log
- 求助
- 截止日期重置问题
- 谁来教我写一个最简单的VC6/ODBC/Sql Server的例子?
- 这样的存储过程怎么写?
- 能否建立这样的约束?
- 郁闷呀!这个问题问了几次了,还是没有解决,这个问题解决不了,项目没法结了!期盼解决中....
- T-sql的问题!
- insert into table(字段1,字段2.....) value(字段1,字段2...)
- 存储过程修改
Returns VarChar(48)
As
Begin
Declare @II Int ,@JJ Int ,@KK Int,@Str VarChar(128)
Select @JJ = 1
Select @Str = 'Not in Handoff, Softer,Soft,Softer Soft, Softer Softer, Soft4 Way, Soft5 Way, Soft6 Way'
Select @II = CharIndex(@S,'0,1,2,3,4,5,6,7')
While @JJ <= (@II - 1) / 2
Begin
Select @KK = CharIndex(',',@Str)
Select @Str = Right(@Str,Len(@Str) - @KK)
Select @JJ = @JJ + 1
End
Select @KK = CharIndex(',',@Str)
Select @Str = LTrim(RTrim(Left(@Str,@KK - 1)))
Return @Str
EndDeclare @tb Table (A VarChar(128),B VarChar(128))
Insert Into @tb
Select '0,1,2,3,4,5,6,7','Not in Handoff, Softer,Soft,Softer Soft, Softer Softer, Soft4 Way, Soft5 Way, Soft6 Way'Select Dbo.Fn_A('2') From @tb
DECLARE @contexts VARCHAR(60),@Thresholds VARCHAR(200),@index INT,@context VARCHAR(20),@Threshold VARCHAR(60)
SET @contexts= '0,1,2,3,4,5,6,7'
SET @Thresholds= 'Not in Handoff, Softer,Soft,Softer Soft, Softer Softer, Soft4 Way, Soft5 Way, Soft6 Way'SELECT Threshold = LTRIM(RTRIM(t.v))
FROM dbo.Split(@contexts,',') c
INNER JOIN dbo.Split(@Thresholds,',') t
ON c.i = t.i
WHERE c.v = '1'
其中,表值函数dbo.Split的定义:CREATE FUNCTION dbo.Split(
@string nvarchar(max),
@separator nvarchar(10) = N','
)
RETURNS TABLE
RETURN
SELECT
i = ROW_NUMBER() OVER(ORDER BY (SELECT 1)),
v = x.n.value('.','nvarchar(50)')
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = @string FOR XML PATH('')),@separator,'</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: gguozhenqian
-- Create date: 2010-8-27
-- Description: 返回context 名称
-- =============================================
create FUNCTION [dbo].[fn_GetContext]
(
@context VARCHAR(100),
@Thresholds VARCHAR(100),
@Res VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
IF(CHARINDEX('max',@Res)<1 and LEN(@Res)>1)
BEGIN
DECLARE @index INT,@Threshold VARCHAR(60),
@startindex INT,@nextindex INT,@length int , @str1 VARCHAR(60)
SET @str1=LEFT(@Thresholds,CHARINDEX(@context,@Thresholds))
SET @index=LEN(@str1)-LEN(REPLACE(@str1,',',''))
SELECT @startindex =dbo.fn_char_index(@Res,',',@index)+1,
@nextindex=dbo.fn_char_index(@Res,',',@index+1),
@length=@nextindex-@startindex
SET @context =SUBSTRING(@Res,@startindex,@length)
END
RETURN @context
END
--@string:待查找字符串,@index:查找位置
returns smallint
as
begin
declare
@i tinyint,--当前找到第@i个
@position tinyint--所在位置
set @position=1;
set @i=0;
while charindex(@char,@string,@position)>0
begin
set @position=charindex(@char,@string,@position)+1;
set @i=@i+1;
if @i=@index
begin
return @position-1;
end
end
return 0;--0表示未找到
end