I can't replace every 2 characters of a string with a '.'

select STUFF('abcdefghi', 3, 1, '.') c3,STUFF('abcdefghi', 5, 1, '.') c5,STUFF('abcdefghi', 7, 1, '.') c7,STUFF('abcdefghi', 9, 1, '.') c9

if I use STUFF I should subsequently overlap the strings c3, c5, c7 and c9. but I can't find a method

can you help me?

initial string:

abcdefghi

the result I would like is

ab.de.gh.

the string can be up to 50 characters

CodePudding user response：

Create a numbers / tally / digits table, if you don't have one already, then you can use this to target each character position:

with digits as ( /* This would be a real table, here it's just to test */
    select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))x(n)
), t as (
    select 'abcdefghi' as s
)
select String_Agg( case when d.n%3 = 0 then '.' else Substring(t.s, d.n, 1) end, '')
from t
cross apply digits d
where d.n <Len(t.s)

Using for xml with existing table

with digits as (
    select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))x(n)
),
r as (
    select t.id, case when d.n%3=0 then '.' else Substring(t.s, d.n, 1) end ch
    from t
    cross apply digits d
    where d.n <Len(t.s)
)
select result=(select ''   ch
    from r r2
    where r2.id=r.id
    for xml path('')
)
from r
group by r.id

CodePudding user response：

You can try it like this:

We use a declared table to have some tabular sample data

DECLARE @tbl TABLE(ID INT IDENTITY, SomeString VARCHAR(200));
INSERT INTO @tbl VALUES('')
                      ,('a')
                      ,('ab')
                      ,('abc')
                      ,('abcd')
                      ,('abcde')
                      ,('abcdefghijklmnopqrstuvwxyz');

--the query

WITH recCTE AS
(
    SELECT ID
          ,SomeString 
          ,(LEN(SomeString) 1)/2 AS CountDots
          ,1 AS OccuranceOfDot
          ,SUBSTRING(SomeString,3,LEN(SomeString)) AS RestString
          ,CAST(LEFT(SomeString,2) AS VARCHAR(MAX)) AS Growing 
    FROM @tbl 

    UNION ALL

    SELECT t.ID
          ,r.SomeString
          ,r.CountDots
          ,r.OccuranceOfDot 2
          ,SUBSTRING(RestString,3,LEN(RestString)) 
          ,CONCAT(Growing,'.',LEFT(r.RestString,2)) 
    FROM @tbl t
    INNER JOIN recCTE r ON t.ID=r.ID
    WHERE r.OccuranceOfDot/2<r.CountDots-1
)
SELECT TOP 1 WITH TIES ID,SomeString,Growing 
FROM recCTE
ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY OccuranceOfDot DESC);

--the result

1   
2   a
3   ab
4   ab.c
5   ab.cd
6   ab.cd.e
7   ab.cd.ef.gh.ij.kl.mn.op.qr.st.uv.wx.yz

The idea in short

• We use a recursive CTE to walk along the string
• we add the needed portion together with a dot
• We stop, when the remaining length is to short to continue
• a little magic is the ORDER BY ROW_NUMBER() OVER() together with TOP 1 WITH TIES. This will allow all first rows (frist per ID) to appear.

Using STUFF()

You can, but you must start at the end:

--the query

WITH appendTally AS
(
    SELECT t.ID
          ,t.SomeString
          ,tally.nmbr
    FROM @tbl t
    OUTER APPLY(SELECT TOP((LEN(t.SomeString) 1)/2) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
                FROM master..spt_values
                ) tally(nmbr)
)
,recCTE As
(
    SELECT TOP 1 WITH TIES 
           ID
          ,CASE WHEN ISNULL(nmbr,0)=0 
                THEN SomeString 
                ELSE CAST(STUFF(SomeString,nmbr*2 1,0,'.') AS VARCHAR(MAX)) END AS SomeString 
          ,nmbr
    FROM appendTally
    ORDER BY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY nmbr DESC)

    UNION ALL

    SELECT t.ID
          ,CASE WHEN ISNULL(t.nmbr,0)=0 
                THEN r.SomeString 
                ELSE CAST(STUFF(r.SomeString,t.nmbr*2 1,0,'.') AS VARCHAR(MAX)) END AS SomeString 
          ,t.nmbr
    FROM appendTally t
    INNER JOIN recCTE r ON t.ID=r.ID AND t.nmbr=r.nmbr-1
)
SELECT ID,SomeString
FROM recCTE
WHERE nmbr=0
ORDER BY ID;

The idea in short

• We use a CTE to append a tally to each string. The trick is, to use a computed TOP-clause together with ROW_NUMBER() called row-wise by APPLY.
• The next calls a recursive CTE for rescue:
  • We pick the row with the highest insert position
  • We use STUFF() to stuff the dot in the given place
  • We traverse down the string from back to front, inserting a dot in each go.
• A little magic is done by the integer division, which will have the same result for two consecutive values like here

test it

SELECT 0/2
      ,1/2
      ,2/2
      ,3/2
      ,4/2;

The result

0,0,1,1,2,...