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

Calculation Fields

I have a field in a table that contains strings. I have another field in the same table that is boolean. I'm trying to figure out how to set the boolean field to True is the string field contains a sub-string. Can someone help?

5 Community Answers

Matillion Agent  

Ian Funnell —

Hi Dan,

You will need a Calculator component for this. Add a new field and set it to a SQL expression which will return true or false.

For example, this is the syntax you would need on Redshift

CASE WHEN "yourcolumn" LIKE '%somestring%' THEN true ELSE false END

You would then have the choice of updating the target table (using a Table Update component) or else creating a new table with the boolean field set how you want it. If there are many updates then the second approach will probably be more efficient.

Best regards,
Ian


Dan Dow —

That worked, thanks!


Dan Dow —

Hello Ian,
How would I do this same thing is the substring is in a column? For example, I have a column1 that is an email domain and I have a column2 that is a csv list of email domains and I need to see if the domain from column 1 is in the list of domains in column2?


Dan Dow —

I should add, what I am trying to do is something like:

CASE
'%' || case_email_domain || '%'
LIKE
account_email_domains
value = account_name

So if the domain from case_email_domain is in the list of account domains the output is the name of the account.


Matillion Agent  

Laura Malins —

Hi Dan

You can use a very similar case statement to one you have above:

CASE WHEN "account_email_domains " ILIKE ‘%’“case_email_domain "‘%’ THEN true ELSE false END

Thanks
Laura

Post Your Community Answer

To add an answer please login