SQL Server: String Manipulation, Unpivoting
- by OMG Ponies
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}...
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
