高分求解自定义函数写法！

[13617650029]

欲达到的功能描述：
字符串A "1,2,3,4,5,6,7,8,9"
字符串B "5,10,12,13,18,65"
将两个字符串进行比较，如果两个字符串中的任意一项能够跟另外一个字符串匹配，则返回1，反之返回0
例如上面两个字符串，都含有5这一项，那么就应该返回1

我本来写了一个，但是因为引用了自己的另外两个自定义函数，告诉我找不到匹配的函数，我把我写的发上来，大家不一定按照我发的改，因为我写的可能不对，以前没写过sql函数，呵呵，最好是大家帮我写个高效点的函数！谢谢啦！

/*
下面是我写的，其中用到另外两个自定义的函数
getSplitNum 是返回字符串分割成数组后的个数
Split       是返回字符串切割成数组后制定位置的数组值

没搞懂为啥不能引用自己定义的其他函数，大家顺便告诉我怎么回事，应该如何引用自己的函数呢？
*/
CREATE FUNCTION [dbo].[isHaved]
 (@strOne nvarchar(4000),@strTwo nvarchar(4000),@splitStr varchar(10))  
RETURNS int
AS  
BEGIN 
declare @countOne int
declare @countTwo int
declare @indexR  int
declare @indexI  Int
declare @indexE Int 
Set @countOne = getSplitNum(@strOne,@splitStr)
Set @countTwo = getSplitNum(@strOne,@splitStr)
Set @indexR = 0
Set @indexI = 0
Set @indexE = 0
While @indexI < @countTwo
	BEGIN
		While @indexR = 1 Or  @indexE < @countOne
			BEGIN
				If Split(@strTwo,@splitStr,@indexI)=Split(@strOne,@splitStr,@indexE)
					@indexR = 1
					break
				@indexE = @indexE +1
			End
		@indexI = @indexI+1
	END
	return @indexR
END

[lgxyz]

引用自定义函数，
那只能把这个函数改成过程来写吧

[dawugui]

/*
功能：实现split功能的函数
*/

create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as 

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return 
end
go

--调用
if exists(select 1 from
(select a from dbo.fn_split('1,2,3,4,5,6,7,8,9',',')) m where a in
(select a from dbo.fn_split('5,10,12,13,18,65',',')))
   print '1'
else 
   print '0'

drop function dbo.fn_split 

/*
1
*/

[13617650029]

楼上的 直接帮我写好这个函数成不

辛苦啦

[htl258_Tony]

--try:

CREATE FUNCTION [dbo].[isHaved]
 (@strOne nvarchar(4000),@strTwo nvarchar(4000),@splitStr varchar(10))  
RETURNS int
AS  
BEGIN 
declare @countOne int
declare @countTwo int
declare @indexR  int
declare @indexI  Int
declare @indexE Int 
Set @countOne = dbo.getSplitNum(@strOne,@splitStr)
Set @countTwo = dbo.getSplitNum(@strOne,@splitStr)
Set @indexR = 0
Set @indexI = 0
Set @indexE = 0
While @indexI < @countTwo
    BEGIN
        While @indexR = 1 Or  @indexE < @countOne
            BEGIN
                If dbo.Split(@strTwo,@splitStr,@indexI)=dbo.Split(@strOne,@splitStr,@indexE)
                   and @indexR = 1
                    break
                  set @indexE = @indexE +1
            End
       set @indexI = @indexI+1
    END
    return @indexR
END

[让你望见影子的墙]

Set @countOne = dbo.getSplitNum(@strOne,@splitStr)
Set @countTwo = dbo. getSplitNum(@strOne,@splitStr)

或者

select @countOne = dbo.getSplitNum(@strOne,@splitStr),@countTwo = dbo. getSplitNum(@strOne,@splitStr)

[chuifengde]

CREATE FUNCTION CompareStr(@str1 VARCHAR(100),@str2 VARCHAR(100))
RETURNS BIT
AS
BEGIN
	DECLARE @r bit

	if exists(SELECT 1 FROM 
	(
	SELECT SUBSTRING(@str1+',',langid,CHARINDEX(',',@str1+',',langid)-langid) a
	FROM [master].dbo.syslanguages s
	WHERE SUBSTRING(','+@str1,langid,1)=','	
	)aa
	INNER JOIN 	
	(
		SELECT SUBSTRING(@str2+',',langid,CHARINDEX(',',@str2+',',langid)-langid) b
		FROM [master].dbo.syslanguages s
		WHERE SUBSTRING(','+@str2,langid,1)=','	
	)bb
	ON aa.a=bb.b) 
		SET @r=1
	ELSE 
		SET @r=0
		
	RETURN @r
	
END

GO 
SELECT dbo.comparestr('1,2,3,4,15,6,13,8,9','5,10,12,13,18,65')

--result
/*---- 
1

（所影响的行数为 1 行）*/

[WEL04]

呵呵 大乌龟 脑子真好用

你这个不用写特别的函数 就可以解决木

[s_hluo]

DECLARE @strA VARCHAR(1000)
DECLARE @strB VARCHAR(1000)
SET @strA = '1,2,3,4,5,6,7,8'
SET @strB = '5,10,12,13,18,65,9'

DECLARE @xmlA XML
DECLARE @xmlB XML
SET @xmlA =  '<root><item>' + REPLACE(@strA, ',', '</item><item>') + '</item></root>'
SET @xmlB =  '<root><item>' + REPLACE(@strB, ',', '</item><item>') + '</item></root>'

IF EXISTS(SELECT T1.item.value('.', 'VARCHAR(1000)') AS 'CommonValue'
	FROM @xmlA.nodes('/root/item') T1(item) JOIN @xmlB.nodes('/root/item') T2(item) 
		ON RTRIM(LTRIM(T1.item.value('.', 'VARCHAR(1000)'))) = RTRIM(LTRIM(T2.item.value('.', 'VARCHAR(1000)'))))
	BEGIN
		SELECT 1
	END
ELSE
	BEGIN
		SELECT 0
	END

[lg3605119]

create function s(@str1 varchar(8000),@str2 varchar(8000),@split varchar(10))
returns int
as
begin
declare @i int
declare @j int
declare @s varchar(100)
declare @b int
set @b=0
set @i = 1
set @j = charindex(@split,@str1+@split)
while(@j>0)
begin
select @s=substring(@str1,@i,charindex(@split,@str1+@split)-1)
set @b= charindex(@split+@s+@split,@split+@str2+@split)
if(@b>0)
set @b=1
break

select @str1=stuff(@str1,@i,charindex(@split,@str1+@split),'')
set @j = charindex(@split,@str1+@split)
end
return @b
end

select dbo.s('1,2,555,4,5','7,90,3,4,87,11',',')

[律己修心]

UP
学学鸟儿的

[叶子]

go
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            set @c = stuff(@c,1,charindex(@split,@c),'') 
           -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))     
        end   
      insert @t(col) values (@c)   
      return   
end

go
create function dbo.compare(@a varchar(200),@b varchar(200))
returns int
as
begin
declare @s int
select @s=count(*) from dbo.m_split(@a,',') where col in
(select col from dbo.m_split(@b,','))

if(@s>0)
begin
set @s=1
return @s
end
else
set @s=0
return @s
end

 
/*测试
select dbo.compare('1,2,3,4,5,6,7,8,9','5,10,12,13,18,65')
--1
select dbo.compare('1,2,3,4,5,6,7,8,9','34,10,12,13,18,65')
--0
*/

[zhoulehua]

2楼不错

[haitao]

如果数字不太大，可以先建一个表变量，插入1..n这n条记录，然后。。。。。。。

declare @tb table(fd int)
declare @i int
set @i=1
while @i<100
begin
  insert @tb values (@i)
  set @i=@i+1
end

declare @sql varchar(1000)
set @sql='select fd from @tb where fd in ('+@str1+') and fd in ('+@str2+')'
........

[牙签是竹子的]

[haitao]

declare @tb table(fd int)
declare @i int
set @i=1
while @i<100
begin
  insert @tb values (@i)
  set @i=@i+1
end

declare @s1 varchar(1000) , @s2 varchar(1000)
set @s1='1,2,3,4'
set @s2='4,5,6,7'


set @s1=','+@s1+','
set @s2=','+@s2+','
select fd from @tb where charindex(','+cast(fd as varchar(10))+',',@s1)<>0 and charindex(','+cast(fd as varchar(10))+',',@s2)<>0

[shaocui]

2楼不错

[肥龙上天]

alter function fn_getStr
(@str1 varchar(100),@str2 varchar(100))
returns  int
as
begin
	declare @R int,@TStr varchar(8)
	set @R = 0
	while charindex(',',@str1) > 0
		begin
			set @TStr = left(@str1,charindex(',',@str1)-1) 
			if charindex(','+@TStr+',',','+@str2+',') > 0
				begin
					set @R = 1
					return @R
				end
			set @str1 = right(@str1,len(@str1)- charindex(',',@str1))
		end
	if charindex(','+@str1+',',','+@str2+',') > 0
		set @R = 1
	return @R
end

declare @str1  varchar(50), @str2  varchar(50)
set @str1 = '1,2,3,4,5,6,7,8,9'
set @str2 = '5,10,12,13,18,65'

select dbo.fn_getStr(@str1,@str2)

/*

-----------
1

(1 row(s) affected)
*/

[claro]

帮顶

[exinke]

declare @str1 varchar(100),@str2 varchar(100),@SQL Nvarchar(200),@a int
set @str1='1,2,3,4,5,6,7,8,9';
set @str2 = '5,10,12,13,18,65';

SET @SQL = N'select @result = patindex(''%['+ @STR1+']%'','''+@STR2 +''') ';

exec sp_executesql @SQL ,N'@result int output ', @Result = @a output

SELECT @a

[13617650029]

不好意思~各位~大家这么热心的给我回复，我却因为工作繁忙把这个事情给忘了，今天才想起来，之前我用另一种方法解决了，不过还是真的感谢大家，特意多加了100分，见者有份，每人10分，再次感谢大家了！~