-- =============================================

-- Author: Jesse McLain

-- Create date: 20090520

-- Description: Just like SUBSTRING, except no 3rd param

-- =============================================

CREATE FUNCTION [dbo].fn_SubstringRest

(

@String varchar(max), -- the base string from which to work

@From int -- starting point (inclusive), of substring to return

)

RETURNS varchar(max)

AS

BEGIN

DECLARE @rtrn varchar(max)

IF LEN(@String) >= @From

SET @rtrn = SUBSTRING(@String, @From, LEN(@String) - @From + 1)

RETURN @rtrn

END

GO

/****************************************************************************

** File: "fn_GetMChooseNCombos.sql"

** Desc: function to provide a "m choose n" functionality for evaluating

** different combinations of size 'm' given a starting set of size 'n'.

**

** Return values:

**

** Called by:

**

** Parameters:

** Input

** ----------

** none

**

** Output

** -----------

** none

**

** Auth: Jesse McLain

** Email: mailto:jesse@jessemclain.com

** Web: http://www.jessemclain.com

** Blog: http://www.jessesql.blogspot.com

**

** Date: 06/30/2009

**

*****************************************************************************

** Change History

*****************************************************************************

** Date: Author: Description:

** -------- -------- -------------------------------------------

** 20090630 Jesse McLain Created script for problem described in page

http://stackoverflow.com/questions/127704/algorithm-to-return-all-combinations-of-k-elements-from-n

*****************************************************************************

** ToDo List

*****************************************************************************

** Date: Author: Description:

** -------- -------- -------------------------------------------

** 20090630 Jesse McLain Created script

*****************************************************************************

** Notes

*****************************************************************************

**

****************************************************************************/

CREATE FUNCTION dbo.fn_GetMChooseNCombos

(

@LettersToPickFrom varchar(max), -- this is the "m" portion

@NumLettersToPick int, -- this is the "n" portion

@LettersAlreadyPicked varchar(max) = '' -- leave blank; function uses internally in recursion

)

RETURNS @retSubWords TABLE (Word varchar(max))

AS

BEGIN

DECLARE @NumLettersPicked int; SET @NumLettersPicked = 0

IF @NumLettersToPick > 0 AND @NumLettersToPick <= LEN(@LettersToPickFrom + @LettersAlreadyPicked)

IF @NumLettersToPick = LEN(@LettersAlreadyPicked + @LettersToPickFrom)

INSERT @retSubWords VALUES (@LettersAlreadyPicked + @LettersToPickFrom)

ELSE

WHILE @NumLettersPicked <= LEN(@LettersToPickFrom)

BEGIN

DECLARE @Letter char(1); SET @Letter = SUBSTRING(@LettersToPickFrom, @NumLettersPicked + 1, 2)

SET @NumLettersPicked = @NumLettersPicked + 1

IF @NumLettersToPick = LEN(@LettersAlreadyPicked + @Letter)

INSERT @retSubWords VALUES (@LettersAlreadyPicked + @Letter)

ELSE

BEGIN

INSERT @retSubWords

SELECT Word FROM dbo.fn_GetMChooseNCombos(

dbo.fn_SubstringRest(@LettersToPickFrom, @NumLettersPicked + 1),

@NumLettersToPick,

@LettersAlreadyPicked + @Letter)

END

END

RETURN

END