November 18, 2014 at 11:06 am
I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.
declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values
(1, 'wfTask=="Issuance" && wfStatus=="Issued" ^ var params =aa.util.newHashMap(); params.put("PermitNumber", capId.getCustomID());displayReportFromWorkflow("General Permit", params);'),
(2, 'wfTask == "Issuance" && wfStatus == "Issued" || wfTask == "Review" && wfStatus == "Denied" ^ var params = aa.util.newHashMap(); displayReportFromWorkflow("Capacity Letter Type III", params);')
Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III
I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.
Unfortunately, CLR is not an option.
Any help is appreciated.
Don Simpson
November 19, 2014 at 6:39 am
I can't think of any way to generate the extra record that duplicates information from what would be the "previous" record, and have it happen in a query. You could do this with a cursor, but a VBScript might be faster if the number of records of varchar information is large. An SSIS package might be in order...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply