Hello I need to translate this query from an Oracle Database to MSSQL and get the exact same result:
WHEN REGEXP_LIKE(E.EVENTS, 'selfServe:[^:]*:completed[:]').
My following attempts have all failed:
WHEN EVENTS LIKE '%[:]selfServe[:][^:]%[:]completed[:]%'
EVENTS LIKE '%[:]selfServe[:]%[^:][:]completed[:]%'
WHERE PATINDEX('selfServe:[^:]*:completed[:]', EVENTS) != 0
WHERE PATINDEX('selfServe:[^:]%:completed[:]', EVENTS) != 0 .
Examples:
This should not match:
OpenQ,
Payment,
Payment:selfServe:Payment-Cancel_Scheduled:initiated::,
Payment:authentication:Authentication:initiated::,
Payment:authentication:Authentication:completed::,
HUP
While this should match:
OpenQ2,
Payment,
Payment:selfServe:Payment:initiated::,
Payment:authentication:Authentication:initiated::,
Payment:authentication:Authentication:initiated::,
Payment:authentication:Authentication:completed::,
Payment:selfServe:Payment:completed::,
HUP
In the first case I have authentication:completed but not selfServe:Payment:completed.
Personally I would tackle this with a string splitting function that looks for the appropriate selfServe:...:completed within one of the comma delimited strings. If you are using a fairly modern version of SQL Server, you may be able to do this using the built in string_split function:
declare @t table(id int,t varchar(1000));
insert into @t values
(1,'OpenQ,Payment,Payment:selfServe:Payment-Cancel_Scheduled:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,HUP')
,(2,'OpenQ2,Payment,Payment:selfServe:Payment:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,Payment:selfServe:Payment:completed::,HUP');
select t.id
,s.value
,t.t
from @t as t
cross apply string_split(t.t,',') as s
where case when patindex('%:selfServe:%',s.value) > 0
and patindex('%:completed:%',s.value) > 0
then 1
else 0
end = 1;
+----+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | value | t |
+----+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2 | Payment:selfServe:Payment:completed:: | OpenQ2,Payment,Payment:selfServe:Payment:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,Payment:selfServe:Payment:completed::,HUP |
+----+---------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
If not, you will need to roll your own string splitter. I am going to assume that you may have some fairly long strings here (more than 4000 characters) and am therefore using an XML based splitter that works well with max data types.
As you are doing this within a BI tool that I am assuming will not allow you to make a new Table Valued Function within the database, you will need a fairly complex statement to process your data, that incorporates the string splitter inline:
declare @t table(id int,t varchar(1000));
insert into @t values
(1,'OpenQ,Payment,Payment:selfServe:Payment-Cancel_Scheduled:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,HUP')
,(2,'OpenQ2,Payment,Payment:selfServe:Payment:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,Payment:selfServe:Payment:completed::,HUP');
with s as
( -- Convert the string to an XML value, replacing the delimiter with XML tags
select id
,t
,convert(xml,'<x>' + replace((select '' + t for xml path('')),',','</x><x>') + '</x>').query('.') as s
from @t
)
select id
,item
,t -- Select the values from the generated XML value by CROSS APPLYing to the XML nodes
from(select id
,t
,n.x.value('.','nvarchar(max)') as item
from s
cross apply s.nodes('x') as n(x)
) a
where case when patindex('%:selfServe:%',a.item) > 0
and patindex('%:completed:%',a.item) > 0
then 1
else 0
end = 1;
+----+-----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | item | t |
+----+-----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2 | Payment:selfServe:Payment:completed:: | OpenQ2,Payment,Payment:selfServe:Payment:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:initiated::,Payment:authentication:Authentication:completed::,Payment:selfServe:Payment:completed::,HUP |
+----+-----------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+