Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Expressions: Case When Substring CharIndex

David__Garcia
Level 8
Level 8

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
David__Garcia
Correct answer by
Level 8
Level 8

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

View solution in original post

3 Replies
saikatk2447661
Level 4
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)))

David__Garcia
Correct answer by
Level 8
Level 8

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

View solution in original post