Sql Server: Apply Regex With Replacement

Here's my SQL query: select codi_nivell from anc_documents Example data is: 06080100000000 06080100000000 06080100000000 06080100000000 06080100000000 06080100000000 060801000000

Solution 1:

SQL Server doesn't support regular expressions. But you can do this using some string manipulations and recursive CTEs:

with ad as (
      selectrow_number() over (orderby code) as seqnum, replace(rtrim(replace(code, '0', ' ')), ' ', '0') as new_code
      from anc_documents
     ),
     cte as (
      select seqnum, new_code, 0asoffset, 0as lev
      from ad
      unionallselect seqnum, stuff(new_code, offset+3, 0, '-'), offset+3, lev +1from cte
      whereoffset+3< len(new_code) 
     )
select*from (selectmax(lev) over (partitionby seqnum) as max_lev, cte.*from cte
     ) cte
where max_lev = lev;

Here is a db<>fiddle.

Solution 2:

You can achieve this by use of XML PATH:

DECLARE@codi_nivell NVARCHAR(100) = (SELECT REVERSE(CAST(REVERSE('06080100000000') ASBIGINT)))

;WITH cte AS(
 SELECT@codi_nivell AS codi_nivell, LEFT(@codi_nivell, 2) AS codi_nivell_left, RIGHT(@codi_nivell, LEN(@codi_nivell)-2) AS codi_nivell_right
 UNIONALLSELECT@codi_nivell, LEFT(codi_nivell_right, 2), RIGHT(codi_nivell_right, LEN(codi_nivell_right)-2)
   FROM cte
   WHERE LEN(codi_nivell_right) >=2
)
SELECT STUFF((SELECT'/'+ c2.codi_nivell_left
  FROM cte c2
  FOR XML PATH('')), 1, 1, '') y
  FROM cte c1
  GROUPBY c1.codi_nivell