如何在sql server中从长度为n的单词构建2 ^ n更改单词

我需要在sql server中使用一个函数来构建下面示例中的所有更改的单词;
对于长度为n的输入字必须构建2 ^ n改变的字;
例如,如果函数的输入是

"I"

函数的输出应该是

I
-

函数的输入是

"am"

函数的输出应该是

am
-m
a-
--

函数的输入是

"sql"

函数的输出应该是

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

解决方法

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,'-') not in (select Word from @T)
end        

select *
from @T

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

或者您可以使用reqursive CTE

declare @Word varchar(10) = 'sql'

;with C as
(
  select @Word as Word,0 as Iteration
  union all
  select cast(stuff(Word,'-') 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

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

更新

如评论中的OP所指出的,递归CTE版本非常慢.使用带有7个字母的单词,CTE返回了960800行.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!