Expressions: Case When Substring CharIndex

[david--garcia]

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 )

florentlb​ Amit_Kumar​ Adhiyan​

[saikatk2447661]

Instead of

Case(When(@source LIKE '<%', Substring(@source,1 , Charindex('<', @source)-1), Else(@source))) 

Try

Case(When(@source LIKE '%<%%', Substring(@source,1 , Charindex('<', @source)-1), Else(@source)))

[florentlb]

Hi David,

Did that help?

Florent

[david--garcia · Accepted solution]

not at all, I resorted to fixing the record's data on a database label