0

I am storing the URL given below in a field in DE:

https://www.tbd.com/tbd-bags/?utm_source=sfmc&utm_medium=email&utm_campaign=_4in1tbd_20170421&utm_term=nav4+bags&utm_id=9087&sfmc_id=33125

From this , i want to trim the utm_campaign value in the field . Finally it should look like this

4in1tbd_20170421

Is this possible through SQL? Can anyone give me a suitable solution please?

1
  • 2
    You should search for regex in SQL. Do you want to store _4in1tbd_20170421 or 4in1tbd_20170421 because the _ might matter. It could be the whole utm_campaign parameter. And stuff like substring etc – Johannes Schapdick Jun 8 '20 at 10:54
1

Not a pretty solution, but I can give you a rather blunt substring approach that replaces everything around your string with "empty string", resulting in only your target string. "Gets the job done" with some assumptions:

Assuming that you know that your string will always be exactly in this order (which is important), then you can work with PATINDEX, LEFT, RIGHT, LEN and REPLACE functions.

PATINDEX returns a number, which represents the position of a certain substring of your string, like indexOf in AMPScript.

===

The approach is: Define a starting marker for your expected result, e.g. 'my text is always right after "utm_campaign=".'

So your marker is "utm_campaign=". The good thing about UTM_parameters is you can pretty much expect them to not get renamed. So I would consider this part rather futureproof.

Replace everything LEFT of the first character of that marker with empty string (which is '').

In the following, similar example, it is done with a "@" as marker. Make sure to subtract the length of the marker (in my case, my starting marker "@" has the length of 1, in your case "utm_campaign=" has a length of 13.)

Imagine you have an emailaddress on all Subscribers:

myaddress@domain.com

/* remove everything left of the @ */
SELECT Replace(EmailAddress,
LEFT(EmailAddress,(PATINDEX('%@%',EmailAddress) - 1)),
'') 
FROM _Subscribers

result:

@domain.com

Next, define an ending marker, e.g. "my text is always right before "&utm_term=".

Replace everything RIGHT of the start of this marker with empty string ('')

/* remove everything right of the @ */
SELECT Replace(EmailAddress,
RIGHT(EmailAddress,LEN(EmailAddress) - LEN(RIGHT(EmailAddress,(PATINDEX('%@%',EmailAddress))))),
'') 
FROM _Subscribers

result:

myaddress@

If you know that your expected string always has a specific length, then I suggest you work with that instead of having an end marker. While an email always has an "@” in the middle, not every link might have "&utm_term=". Potentially using a standardized length of your utm_campaign value instead of an end marker as an indicator where to cut off the string will also work if e.g. utm_term is not in your link for some reason, and would thus be a more stable solution.

0

Why burden the SOQL ?

  1. If you have an apex calling the SOQL , loop through the result set and play around with the field vaue using String methods (substring,split)
  2. Else if you want to process the values after pulling a xlsx, use a macro.

If you can throw light on the exact usage of this SOQL, we can help you further.

1
  • 1
    I’d expect a clairvoyant to notice that this is a Marketing Cloud question ;-) – zuzannamj Jun 8 '20 at 17:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.