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

-- 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