Expressions: Case When Substring CharIndex
I am using the following sql query to remove some unwanted strings.
select case when sSource like '%<%%' then SUBSTRING(sSource, 1,charindex('<' ,SSource)-1) else sSource end
from [xx].[xx].NmsTrackingUrl
It will cleanse the record and remove the string (<% } %>)
Now I am trying to do the same on a worflow but I am having trouble converting the expression.
Case(When(@source LIKE '<%', Substring(@source,1 , Charindex('<', @source)-1), Else(@source)))
The error is the following.. anyone can give me a hand?
I can see that the case expression is being interpreted wrongly by Neolane and it should be 'Case When' not 'Case(When' so I used the following expression instead, it also performs a CASE but the error is different now.
Iif(@source LIKE '<%', Substring(@source,1,Charindex('<%', @source)-1), @source)
05/01/2018 18:21:37 query5 0.sSource , 1 , charindex(T0.sSource, N'<%') - 1) else T0.sSource end FROM NmsTrackingUrl T0 WHERE (T0.iTrackingUrlId > 0) AND ((T0.iTrackingUrlId > 0 OR T0.iTrackingUrlId < 0))' could not be executed.
05/01/2018 18:21:37 query5 SQL statement 'INSERT INTO wkf986963078_204_1 (iId,iDeliveryId,sLabel,iType,iOccurrence,sExpr1515173527) SELECT DISTINCT T0.iTrackingUrlId, T0.iDeliveryId, T0.sLabel, T0.iType, T0.iOccurrence, case when T0.sSource LIKE N'<%' ESCAPE '\' then Substring(T
05/01/2018 18:21:37 query5 ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. SQLState: 01000
05/01/2018 18:21:37 query5 ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQLState: 22001
I am running on w3schools testing page and it works fine.
I am using the following resources (Adobe Campaign Help | List of functions and Adobe Campaign Help | Advanced expression editing )