Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

How do I replace a value in a string?

Is there a transformation component that can be used to replace a value in a string? Say I have a number provided with a comma instead of a decimal point and I want to replace the character, how do I do this?

4 Community Answers

Graham Lannigan —

Never mind, I've got it - string functions are available in the "Calculator" transformation.

The name's a little misleading I think!


Matillion Agent  

Ian Funnell —

Hi Graham,

Matillion is an ELT tool, so these kinds of transformations would be performed inside Redshift, using SQL commands generated by components in a Transformation job.

The Calculator is the component to use for this purpose, and I’ve attached a small example (StringManipulation.json) which you can import (Project/Import) to demonstrate a couple of different ways to do this.

Best regards,
Ian


Kevin Havice —

I was just looking for a way to replace NULL values with 0, for a metric column in a fact table; and I found this post.

I see how you can use the Calculator component for replacing / substituting values within a column. But the Calculator component is described as being used for adding new columns; and that's how I think of it.

I think it would be more intuitive to have a separate "Replace" or "Substitute" component, for replacing values within an existing column. Whether it be replacing NULL with something else; or substituting a string (or sub-string!) in an existing text column for some other string; etc.


Matillion Agent  

Harpreet Singh —

Hi Kevin,

I understand why it is confusing. When we are building the transformation we use the calculator that shows original and calculated columns until we create a final table with the columns we are interested in. This is usually helpful for the developer to look at the data and see both values in the data set. Since calculator component could manage all that it would be mute to have another component do the same function.

Best Regards

Harpreet Singh

Post Your Community Answer

To add an answer please login