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 the whole changed word in the example below; for an input word of length n should construct a 2 ^ n modified word; For example, if the input of a function

"I" 

function output should be

 I - 

function input

 "am" 

function output should be

 am -m a- -- 

function input

 "sql" 

function output should be

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

Answer 1

You can do this with a table of numbers (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 recursive 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/

Refresh

The recursive version of CTE is very slow, as the OP points out in a comment. Using a 7-letter word, 960,800 lines are 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 

outputs the following output

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

I leave this for you to enable the feature.

See how this data.se query works