Formula for contains

[Kuroshi]

I have a formula in my table.
CASE WHEN [@field:UserName] = 'Emp'
THEN 'Employee'
ELSE 'Manager'

I wanted to make that if UserName starts with Emp then it is employee otherwise Manager

[Barbie]

Hi, 

please try this instead:

CASE WHEN [@field:UserName] LIKE '%Emp'
THEN 'Employee'
ELSE 'Manager'
END

hope this works

-Barbie

[DefinitelyNot31337]

Hello @Barbie,

 

If you are working with big data, you may want to use the syntax below instead. It works well with your requirement and runs much faster on a database standpoint.

CASE 
  WHEN LEFT([@field:usz], 3) = 'Emp'
  THEN 'Employee'
  ELSE 'Manager'
END

 

Hope this helps

-DN31337!

[Flowers4Algernon]

Hello, just wanted to share this Function Reference that will be definitely a big help on calculations: https://howto.caspio.com/function-reference/

[Queso]

Thank you for the suggestions. I tried the solutions above. 

It seems that Barbie's solution outputs a 'Yes' if the text 'emp' is placed anywhere on the username.  But for DefinitelyNot31337's solution, it outputs a 'Employee' if the username starts with 'emp'. 

Solutions are great so I utilized them both. Here is a reference as well: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/#:~:text=Birth. Learn more.-,Conditional Cases in Calculated Fields,-You can use

[Wikiwi]

@trickson you can also try Charindex() for this. Charindex will output the starting point of the string or the character that you are looking for and if that value is greater than 0 that means that there is an Emp in the string. 

CASE 
  WHEN Charindex('Emp',[@field:Name]) > 0
  THEN 'Employee'
  ELSE 'Manager'
END

[GoodBoy]

Hello! Another approach is by using the SUBSTRING function to check if the 1st three letters of the field is 'Emp'.

CASE 

WHEN SUBSTRING([@field:UserName], 1, 3) = 'Emp'

THEN 'Employee'

ELSE 'Manager'

END