Remove characters in tabel


#1

Hello,

I am merging my Google analytics from my website and app.

However, at the end of our news articles page names, each platform has a different unique identifier.

Is there a way to edit a column and remove a certain amount of characters?

App Analytics - remove the last 16 characters

Web Analytics - Remove the last eight characters

Thanks

Damon


#2

Hi Damon!

Yes, you can the substr function and length function in SQLite to do this.

You can edit the column > custom formula > and input this in your custom formula:

substr(“ga:screenName”, 1, length(“ga:screenName”) - 16 )

You can use length(“ga:screenName”) to calculate the character placement 16 spaces away from the last place.

The substr function will then take the ga:screenName as an input and only return the characters starting from the 1 position to the X - 16 position where X is the last character position.

If your ga:screenName is less than 16 characters then you would need to a case statement to handle that case.

Let me know if you have any questions!

Jen


#3

Thanks Jennifer, I will give it a try.

Regards

Damon


#4

Hi Jennifer,

When I insert the formula, it comes back with the following error.


#5

Hey Damon,

Looks like the double quotes are not actually double quotes. Can you replace all the double quotes and try running the formula again?

Thank you,

Jennifer