SQL Server: String Manipulation, Unpivoting

I have a column called body, which contains body content for our CMS. The data looks like:

…{cloak:id=1.1.1}…{cloak}…{cloak:id=1.1.2}…{cloak}…{cloak:id=1.1.3}…{cloak}…

  • Auto increment a non-primary key (SQL Server 2014) in C#
  • How can I find out what FOREIGN KEY constraint references a table in SQL Server?
  • SQL Server query dry run
  • how to set the default value of the column when there is null value in stored procedure
  • Saving changes after table edit in SQL Server Management Studio
  • SQL Server replication and application queries/updates conflicts resulting in deadlocks
  • A moderately tweaked for readability example:

    ## h5. A formal process for approving and testing all external network connections and changes to the firewall and router configurations? 
    {toggle-cloak:id=1.1.1}{tree-plus-icon} *Compliance:* {color:red}{*}Partial{*}{color} (?) 
    {cloak:id=1.1.1} || Date: | 2010-03-15 || || Owner: | Brian || || Researched by: | || || Narrative: | Jira tickets are normally used to approve and track network changes\\ || || Artifacts: | Jira.bccampus.ca\\ || || Recommendation: | Need to update policy that no Jira = no change\\ || || Proposed Remedy(ies): | || || Approved Remedy(ies): | || || Date: | || || Reviewed by: | || || Remarks/comments: | || 
    {cloak}## h5. Current network diagrams with all connections to cardholder data, including any wireless networks? 
    {toggle-cloak:id=1.1.2}{tree-plus-icon} *Compliance:* {color:red}{*}TBD{*}{color} (?) 
    {cloak:id=1.1.2}
    

    I’d like to get the cloak values out in the following format:

     requirement_num
     -----------------
     1.1.1
     1.1.2
     1.1.3
    

    I’m looking at using UNIONs – does anyone have a better recommendation?

    Forgot to mention:

    • I can’t use regex, because CLR isn’t enabled on the database.
    • The numbers aren’t sequencial. The current record jumps from 1.1.6 to 1.2.1
    • I can guarantee that there will be a pair for each requirement number – {toggle-cloak:id=x.y.z} and {cloak:id=x.y.z}. I’m interested in what is between the {cloak:id=x.y.z} and {cloak} tags.

    2 Solutions collect form web for “SQL Server: String Manipulation, Unpivoting”

    I’d probably use a function for this. Something like:

    create function [dbo].[getCloaks]
    (
    @String     varchar(8000)
    )
    returns @tbl table (s varchar(1000))
    as
    begin
    declare @i int, @j int, @k int
        select  @i = 1
        while charindex('{cloak:id=', @String, @i) > 0
        begin
            select @j = charindex('{cloak:id=', @String, @i)
            select @k = charindex('}', @String, @j)
            insert  @tbl select substring(@String, @j + 10, @k - @j - 10)
            select  @i = @k + 1
        end
        return
    end
    

    Here’s a recursive CTE version that will hopefully be a little faster than an iterative UDF:

    DECLARE @Data nvarchar(1000)
    
    SET @Data = N'...{cloak:id=1.1.1}...{cloak}...{cloak:id=1.1.2}...{cloak}...' +
                N'{cloak:id=1.1.3}...{cloak}...'
    
    ;WITH Cloak_CTE AS
    (
        SELECT
            CAST(NULL AS nvarchar(50)) AS requirement_num,
            CHARINDEX('{cloak:id=', @Data) AS start_index,
            CHARINDEX('}', @Data, CHARINDEX('{cloak:id=', @Data)) AS end_index
    
        UNION ALL
    
        SELECT
            CAST(SUBSTRING(@Data, start_index + 10,
                end_index - start_index - 10) AS nvarchar(50)),
            CHARINDEX('{cloak:id=', @Data, end_index + 1),
            CHARINDEX('}', @Data, CHARINDEX('{cloak:id=', @Data, end_index + 1))
        FROM Cloak_CTE
        WHERE start_index > 0
    )
    SELECT requirement_num
    FROM Cloak_CTE
    WHERE requirement_num IS NOT NULL
    

    Should be relatively straightforward to adjust for multiple rows or different patterns, or put this into an inline UDF.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.