--在uf_split函数里加自增列CREATE function uf_split ( @str varchar(8000) --要分拆的字符串 ,@spli varchar(10) --字符串分隔符 ) returns @retab table(id int identity(1,1),istr varchar(8000)) as begin declare @i int declare @splen int select @splen=len(@spli),@i=charindex(@spli,@str) while @i > 0 begin insert into @retab values(left(@str,@i-1)) select @str=substring(@str,@i+@splen,8000) select @i=charindex(@spli,@str) end if @str<>'' insert into @retab values(@str) return endselect id from uf_split('ab|abc|b|cbd|dds|acb','|') where istr='b' /* id ----------- 3(所影响的行数为 1 行) */
我现在有两个字段 A B |1|32|12|27| |122|56.5|100|8|两个字段中的值是一一对应的,就是A的1对应B的122现在要查A中12对应的值大于50的所有记录是这么个情况,实在想不出来……郁闷
gahade(与君共勉)小弟实在不是很懂sql的函数,上面你给的函数能否改成我需要的,请指点,谢谢
declare @str varchar(800),@str1 varchar(100),@i int set @i=0 set @str1='|cbd|' set @str='|ab|abc|b|cbd|dds|acb|' select @str=left(@str,charindex(@str1,@str)) while charindex('|',@str)>0 begin set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str)) set @i=@i+1 end print @i
sdhylj(青锋-SS)这个写成函数怎么写?小弟不会,谢谢,谢谢,呵呵
CREATE FUNCTION dbo.f_1(@str varchar(8000),@str1 varchar(100)) RETURNS int AS BEGIN declare @i int set @i=0 select @str=left(@str,charindex(@str1,@str)) while charindex('|',@str)>0 begin set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str)) set @i=@i+1 end return @i END
邹老大的例子: --分段截取函数 CREATE FUNCTION dbo.f_GetStr( @s varchar(8000), --包含多个数据项的字符串 @pos int, --要获取的数据项的位置 @split varchar(10) --数据分隔符 )RETURNS varchar(100) AS BEGIN IF @s IS NULL RETURN(NULL) DECLARE @splitlen int SELECT @splitlen=LEN(@split+'a')-2 WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0 SELECT @pos=@pos-1, @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'') RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),'')) END GO
declare @str varchar(800),@str1 varchar(100) set @str1='|cbd|' set @str='|ab|abc|b|cbd|dds|acb|' print(dbo.f_1(@str,@str1))输出 -------- 4
MS一句就可以了 declare @str varchar(800),@str1 varchar(100) set @str1='|cbd|' set @str='|ab|abc|b|cbd|dds|acb|'select len(left(@str,charindex(@str1,@str)))-len( replace(left(@str,charindex(@str1,@str)),'|',''))------------- 4
playwarcraft(时间就像乳沟,挤挤还是有的) MS一句就可以了 declare @str varchar(800),@str1 varchar(100) set @str1='|cbd|' set @str='|ab|abc|b|cbd|dds|acb|'select len(left(@str,charindex(@str1,@str)))-len( replace(left(@str,charindex(@str1,@str)),'|',''))------------- 4正解!
(
@str varchar(8000) --要分拆的字符串
,@spli varchar(10) --字符串分隔符
)
returns @retab table(id int identity(1,1),istr varchar(8000))
as
begin
declare @i int
declare @splen int
select @splen=len(@spli),@i=charindex(@spli,@str)
while @i > 0
begin
insert into @retab
values(left(@str,@i-1))
select @str=substring(@str,@i+@splen,8000)
select @i=charindex(@spli,@str)
end
if @str<>'' insert into @retab values(@str)
return
endselect id from uf_split('ab|abc|b|cbd|dds|acb','|')
where istr='b'
/*
id
-----------
3(所影响的行数为 1 行)
*/
A B
|1|32|12|27| |122|56.5|100|8|两个字段中的值是一一对应的,就是A的1对应B的122现在要查A中12对应的值大于50的所有记录是这么个情况,实在想不出来……郁闷
set @i=0
set @str1='|cbd|'
set @str='|ab|abc|b|cbd|dds|acb|'
select @str=left(@str,charindex(@str1,@str))
while charindex('|',@str)>0
begin
set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str))
set @i=@i+1
end
print @i
RETURNS int AS
BEGIN
declare @i int
set @i=0
select @str=left(@str,charindex(@str1,@str))
while charindex('|',@str)>0
begin
set @str=substring(@str,charindex('|',@str)+1,len(@str)-charindex('|',@str))
set @i=@i+1
end
return @i
END
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
set @str1='|cbd|'
set @str='|ab|abc|b|cbd|dds|acb|'
print(dbo.f_1(@str,@str1))输出
--------
4
declare @str varchar(800),@str1 varchar(100)
set @str1='|cbd|'
set @str='|ab|abc|b|cbd|dds|acb|'select len(left(@str,charindex(@str1,@str)))-len( replace(left(@str,charindex(@str1,@str)),'|',''))-------------
4
MS一句就可以了
declare @str varchar(800),@str1 varchar(100)
set @str1='|cbd|'
set @str='|ab|abc|b|cbd|dds|acb|'select len(left(@str,charindex(@str1,@str)))-len( replace(left(@str,charindex(@str1,@str)),'|',''))-------------
4正解!