Expand my Community achievements bar.

SOLVED

Expressions: Case When Substring CharIndex

Avatar

Community Advisor

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.

1388393_pastedImage_5.png

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

The error is the following.. anyone can give me a hand?

1388395_pastedImage_8.png

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.

1388547_pastedImage_0.png

I am using the following resources (Adobe Campaign Help | List of functions and Adobe Campaign Help | Advanced expression editing )

florentlbAmit_KumarAdhiyan

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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

View solution in original post

3 Replies

Avatar

Level 4

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)))

Avatar

Level 10

Hi David,

Did that help?

Florent

Avatar

Correct answer by
Community Advisor

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