This would be so much easier in VB or C#...

Well it's not pretty and not sure if the parameter you pass in is going to be pretty but here goes...

I used Jeff Moden's Tally Splitter code so thanks to Jeff for his many contributions....

no guarantees on performance... 🙂

Not really an unlimited number of parameters... actually only 1 real parameter...

basic idea is to concatenate all the "parameter" values into a single delimited string and then parse and compare inside the function...

kinda works like a multi-parameter function.... =P

The delimiter can be up to 5 characters but you can change that easy enough... maybe use ::::: as the delimiter

GO

if object_id('dbo.MyTestFunction') is not null

drop function dbo.MyTestFunction

GO

Create function MyTestFunction(@pString as varchar(8000),@pDelimiter as varchar(5))

returns bit

as

begin

declare @Count as int;

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT 1 UNION ALL

SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,len(@pDelimiter)) = @pDelimiter

),

cteLen(N1,L1) AS(--==== Return start and length (for use in substring)

SELECT s.N1,

ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)

FROM cteStart s

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

select @Count = count(*)

from (

SELECT SUBSTRING(@pString, l.N1, l.L1) Vals

FROM cteLen l

group by SUBSTRING(@pString, l.N1, l.L1)

) t

if (@Count > 1) Return 1

Return 0

end

GO

--------------------Testing the Function---------------------------

set nocount on;

declare @Table as table (F1 varchar(10), F2 varchar(10), F3 varchar(255), F4 varchar(10))

insert into @Table

values ('a','a','b','b')

,('a','a','a','a')

insert into @Table

values(1, 1, 2,3)

,(1, 1, 1,1)

insert into @Table

values('c','c','c','c')

,('d','d','c','c')

declare @pString as varchar(max)

declare @pDelimiter as varchar(2) = '|'

select F1, F2, F3, F4, dbo.MyTestFunction( F1 + '|' + F2 + '|' + F3 + '|' + F4, @pDelimiter)

from @Table

select F1, F2, F3, dbo.MyTestFunction(F1 + '|' + F2 + '|' + F3 , @pDelimiter)

from @Table

select F1, F2, dbo.MyTestFunction( F3 + '|' + F4, @pDelimiter)

from @Table