[SOLVED] String value shifting in SQL Server

String value shifting in SQL Server

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.

Solution

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.