我需要在sql server中使用一个函数来构建下面示例中的所有更改的单词;
对于长度为n的输入字必须构建2 ^ n改变的字;
例如,如果函数的输入是 @H_301_4@"I" @H_301_4@I -
对于长度为n的输入字必须构建2 ^ n改变的字;
例如,如果函数的输入是 @H_301_4@"I" @H_301_4@I -
函数的输入是
@H_301_4@"am" @H_301_4@am -m a- --函数的输入是
@H_301_4@"sql" @H_301_4@sql -ql s-l sq- --l s-- -q- ---解决方法
您可以使用数字表(master..spt_values)和循环中的
stuff执行此操作.
@H_301_4@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
@H_301_4@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 Chttp://data.stackexchange.com/stackoverflow/q/122337/
更新
如评论中的OP所指出的,递归CTE版本非常慢.使用带有7个字母的单词,CTE返回了960800行.
