String value shifting in SQL Server

Question

Not sure how to clearly describe what I'd like to achieve, hope my question below does make some sense.

Let's suppose I have the string ABCDE. I would like to iterate as many times as its length to create shifted patterns of the string as depicted below

_BCDE,A_CDE,AB_DE,ABC_E,ABCD_

So I would like to create all the combinations with an underscore in all possible positions in order to use them in a query like

DECLARE @WORD AS NVARCHAR(50)
SET @WORD = 'ABCDE'
SELECT position = PATINDEX(CONCAT('%', @WORD, '%'), 'BGHKAGCDEBABIDEKFABCREDNBSALCDEOPL');

where @WORD should be the words with the _.

I have the feeling that this can be done using a recursive CTE but I'm not sure how to start.

Any help would be much appreciated.

score 3 · Accepted Answer · answered Mar 05 '20 at 22:55

You can use a recursive CTE:

DECLARE @WORD AS NVARCHAR(50);
SET @WORD = 'ABCDE';

with cte as (
      select 1 as n, @word as word
      union all
      select n + 1, word
      from cte
      where n < len(word)
     )
select word, stuff(word, n, 1, '_')
from cte;

Here is a db<>fiddle.

Radagast · Answer 2 · 2020-03-06T01:31:04.930

An attempt without using a Recursive CTE. Handle with care when dealing with large sized strings though, because replicate has its limitations.

with cte1 as (select 'abcde' as txt)

select value, stuff(value, row_number() over (order by value), 1, '_') as permutations
from cte1
cross apply string_split(replicate(txt+'.',len(txt+'.')-1),'.')
where value<>'';

Outputs

value   permutations
abcde   _bcde
abcde   a_cde
abcde   ab_de
abcde   abc_e
abcde   abcd_

DEMO

String value shifting in SQL Server

2 Answers2