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

Expressions: Case When Substring CharIndex

Avatar

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile
David__Garcia
Level 6

05-01-2018

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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile

Avatar
Springboard
Level 6
David__Garcia
Level 6

Likes

106 likes

Total Posts

192 posts

Correct Reply

44 solutions
Top badges earned
Springboard
Establish
Validate 10
Coach
Contributor
View profile
David__Garcia
Level 6

05-02-2018

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

Answers (2)

Answers (2)

Avatar

Avatar
Give Back 50
Level 10
florentlb
Level 10

Likes

237 likes

Total Posts

1,109 posts

Correct Reply

239 solutions
Top badges earned
Give Back 50
Give Back 5
Give Back 3
Give Back 25
Give Back 200
View profile

Avatar
Give Back 50
Level 10
florentlb
Level 10

Likes

237 likes

Total Posts

1,109 posts

Correct Reply

239 solutions
Top badges earned
Give Back 50
Give Back 5
Give Back 3
Give Back 25
Give Back 200
View profile
florentlb
Level 10

25-01-2018

Hi David,

Did that help?

Florent

Avatar

Avatar
Contributor
Level 3
saikatk2447661
Level 3

Likes

22 likes

Total Posts

58 posts

Correct Reply

14 solutions
Top badges earned
Contributor
Shape 1
Give Back 5
Give Back 3
Give Back
View profile

Avatar
Contributor
Level 3
saikatk2447661
Level 3

Likes

22 likes

Total Posts

58 posts

Correct Reply

14 solutions
Top badges earned
Contributor
Shape 1
Give Back 5
Give Back 3
Give Back
View profile
saikatk2447661
Level 3

07-01-2018

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