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

Substring function

Avatar

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile
jael91407431
Level 3

18-09-2017

Can anyone point me in the right direction to create from the advanced SQL list of functions:

listoffunctions.png

...a substring of  Substr(MKTG_PROMO_CELL_KEY) from character 13 to 6 as "source_id" (field)?

So the function formula is "Substring (<String>, <Offset>, <Length>)", how can I select from the above field ("MKTG_PROMO_CELL_KEY") the characters 13 to 6 within that field (to create a new field ,"source_id")?

Thanks.

Finally, can anyone point me to a good online resource that teaches or lists all of these SQL functions. They're are a lot of SQL sites that lists SQL functions, but anyone know a good site that lists all of them found in Adobe Campaign?

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 1
MVP
Jean-Serge_Biro
MVP

Likes

353 likes

Total Posts

464 posts

Correct Reply

153 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile

Avatar
Validate 1
MVP
Jean-Serge_Biro
MVP

Likes

353 likes

Total Posts

464 posts

Correct Reply

153 solutions
Top badges earned
Validate 1
Boost 50
Boost 5
Boost 3
Boost 250
View profile
Jean-Serge_Biro
MVP

19-09-2017

Hi Jael,

The only documentation is:

https://docs.campaign.adobe.com/doc/AC/en/PTF_Creating_queries_Methodology.html


These SQL functions are written in the database underlying language code, and you can find them all in the Adobe Campaign folder node:

Administration>Settings>SQL Script.

Select your own Database system (mySql, Postgres, SQL Server, Oracle, etc) to see how the functions listed are written in the underlying DB engine language (T-SQL for SQL Server, PL/SQL for Oracle, etc).

1301845_pastedImage_1.png

And one very efficient way to learn quickly is to display the generated SQL query (SQL query link, at the bottom of the form) when you define the Query through the UI, especially for date manipulation, it is easier.

Regards.
Jean-Serge

Answers (6)

Answers (6)

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

28-02-2019

Hi,

Looks like the link changed. It's probably that URL now: Steps to create a query

You can also use the search here: Browse Collection

Florent

Avatar

Avatar
Validate 1
Level 1
rng8
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
rng8
Level 1

Like

1 like

Total Posts

2 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
rng8
Level 1

27-02-2019

Hi Jean-Serge,

Is there a new url?

https://docs.campaign.adobe.com/doc/AC/en/PTF_Creating_queries_Methodology.html

404 not found error message:

The requested URL /doc/AC/en/PTF_Creating_queries_Methodology.html was not found on this server.

Thanks.

Avatar

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile
jael91407431
Level 3

10-10-2017

Yes, thank you so much and sorry for the dup.

Jae

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

10-10-2017

Hi Jae

Could you get through your issue following Jean-Serge's suggestions?

(I've locked the duplicate thread)

Thanks,

Florent

Avatar

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile

Avatar
Validate 10
Level 3
jael91407431
Level 3

Likes

13 likes

Total Posts

46 posts

Correct Reply

0 solutions
Top badges earned
Validate 10
Validate 1
Boost 5
Boost 3
Boost 10
View profile
jael91407431
Level 3

02-10-2017

Hi Amit,

Sorry for the duplicate post, I believe it was in error as I must of clicked on post it twice.

Sorry and thank you and the forum moderators for your continual help!

Sincerely,

Jae

Avatar

Avatar
Validate 10
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Validate 10
Validate 1
Establish
Give Back 50
Give Back 5
View profile

Avatar
Validate 10
MVP
Amit_Kumar
MVP

Likes

329 likes

Total Posts

649 posts

Correct Reply

231 solutions
Top badges earned
Validate 10
Validate 1
Establish
Give Back 50
Give Back 5
View profile
Amit_Kumar
MVP

21-09-2017

Hi, Jael,

Please don't ask the same question in multiple threads, use one thread and If you need something urgent tag one of us(florentlb, Jean-Serge Biron Adhiyan Vipul Raghav david_garcia ) but please don't create duplicate threads.

florentlb, Can you close/merge the duplicate threads in one, similar to this question?

You can follow the instructions given above and extend xtk:funcList schema, Use this custom schema to enable your namespace in the default function list. The default supported names space are 'xtk','nl','nms','ncm', 'crm' and you can extend this list for your use case.

regards,

Amit