Find Substring - SQL

Question

I need to find a substring that is in a text field that is actually partially xml. I tried converting it to xml and then use the .value method but to no avail.

The element(substring) I am looking for is a method name that looks like this:

AssemblyQualifiedName="IPMGlobal.CRM2011.IPM.CustomWorkflowActivities.ProcessChildRecords,

where the method at the end "ProcessChildRecords" could be another name such as "SendEmail". I know I can use the "CustomWorkflowActivities." and the , (comma) to find the substring (method name) but not sure how to accomplish it. In addition, there may be more that one instance listed of the **"CustomWorkflowActvities.<method>"**

Some Clarifications:

Below is my original query. It returns that first occurrence in each row but no additional. For example I might have in the string '...IPM.CustomWorkflowActivities.ProcessChildRecords...' and '...IPM.CustomWorkflowActivities.GetworkflowContext...'

The current query only returns Approve Time Process, ipm_mytimesheetbatch, ProcessChildRecords

SELECT WF.name WFName, ( SELECT TOP 1 Name FROM entity E WHERE WF.primaryentity = E.ObjectTypeCode ) Entity, Convert(xml, xaml) Xaml, SUBSTRING(xaml, Charindex('CustomWorkflowActivities.', xaml) + Len('CustomWorkflowActivities.'), Charindex(', IPMGlobal.CRM2011.IPM.CustomWorkflowActivities, Version=1.0.0.0', xaml) - Charindex('CustomWorkflowActivities.', xaml) - Len('CustomWorkflowActivities.')) FROM FilteredWorkflow WF

WHERE 1 = 1 AND xaml LIKE '%customworkflowactivities%' AND statecodename = 'Activated' AND typename = 'Definition' ORDER BY NAME

Take a look at the `LIKE` operator in your RDBMS. Also, many RDBMSes have a regexp match function. It's not fast, though, because an an index by that field cannot be used. — 9000, Jan 23 '18 at 16:13
What are the actual field contents? How did you try to extract the string and what are you trying to do? — Panagiotis Kanavos, Jan 23 '18 at 16:41

Lukasz Szozda · Answer 1 · 2018-01-23T17:13:54.877

0

If you are using Oracle you could use REGEXP function:

WITH cte(t) as (
  SELECT 'AssemblyQualifiedName="IPMGlobal.CRM2011.IPM.CustomWorkflowActivities.ProcessChildRecords,' FROM dual
)
SELECT t,
     regexp_replace(t, '.*CustomWorkflowActivities.(.+)\,.*', '\1') AS r
FROM cte;

DBFiddle Demo

SQL Server:

WITH cte(t) as (
  SELECT 'AssemblyQualifiedName="IPMGlobal.CRM2011.IPM.CustomWorkflowActivities.ProcessChildRecords,asfdsa'
)
SELECT t,SUBSTRING(t, s, CHARINDEX(',', t, s)-s)
FROM (SELECT t, PATINDEX( '%CustomWorkflowActivities.%', t) + LEN('CustomWorkflowActivities.') AS s
      FROM cte 
) sub;

DBFiddle Demo 2

edited Jan 23 '18 at 17:13

answered Jan 23 '18 at 16:34

Lukasz Szozda

162,964
23
234
275

Sorry SQL Server. – PickyTech Jan 23 '18 at 17:06
Very much on the right track but, Sorry I forgot to mention, the xml may have more than one occurrence for method names. – PickyTech Jan 24 '18 at 16:59

Find Substring - SQL

1 Answers1