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-
---
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With