how to build 2^n changed word from word with length n in sql server

Question

I need a function in sql server to build all of changed word in below example; for input word with length n must build 2^n changed word; For example, if the input of the function is

"I"

the output of the function should be

I
-   

the input of the function is

"am"

the output of the function should be

am
-m
a-
--

the input of the function is

"sql"

the output of the function should be

sql
-ql
s-l
sq-
--l
s--
-q-
--- 

Answer 1

You can do this with a numbers table (master..spt_values) and stuff in a loop.

declare @Word varchar(10) = 'sql'

declare @T table
(
  Word varchar(10)
)

insert into @T values (@Word)

while not exists(select *
                 from @T 
                 where Word = replicate('-', len(@Word)))
begin              
  insert into @T(Word)
  select distinct stuff(T.Word, N.number, 1, '-')
  from @T as T
    cross join
       master..spt_values as N
  where N.type = 'P' and
        N.number between 1 and len(@Word) and
        stuff(T.Word, N.number, 1, '-') not in (select Word from @T)
end        

select *
from @T

https://data.stackexchange.com/stackoverflow/q/122334/

Or you can use a reqursive CTE

declare @Word varchar(10) = 'sql'

;with C as
(
  select @Word as Word,
         0 as Iteration
  union all
  select cast(stuff(Word, N.number, 1, '-') as varchar(10)),
         Iteration + 1
  from C
    cross join
       master..spt_values as N
  where N.type = 'P' and
        N.number between 1 and len(@Word) and
        Iteration < len(@Word)
)
select distinct Word
from C

https://data.stackexchange.com/stackoverflow/q/122337/

Update

The recursive CTE version is really slow as pointed out by OP in a comment. Using a word with 7 letters there are 960800 rows returned from the CTE.

Answer 2

This Recursive CTE

declare @input varchar(25)

set @input = 'SQL'
;WITH cte 
     AS (SELECT Stuff(@input, v.NUMBER, 1, '-') OUTPUT, 
                0                               LEVEL 
         FROM   MASTER..spt_values v 
         WHERE  TYPE = 'P' 
                AND NUMBER BETWEEN 1 AND Len(@input) 
         UNION ALL 
         SELECT Stuff(cte.OUTPUT, v.NUMBER, 1, '-') OUTPUT, 
                cte.LEVEL + 1                       AS LEVEL 
         FROM   MASTER..spt_values v, 
                cte 
         WHERE  TYPE = 'P' 
                AND cte.LEVEL + 1 < Len(@input) 
                AND NUMBER BETWEEN 1 AND Len(@input)) SELECT DISTINCT OUTPUT 
FROM   cte 
UNION 
SELECT @INPUT 
ORDER  BY OUTPUT 

produces the following output

---    
--l    
-q-    
-ql    
s--    
s-l    
sq-    
sql

I leave it to you to put into a function.

See it working at this data.se query