Description: The substring() function extracts part of a String and has several variations on syntax. If the String input is a null value, the formula will return “”.

Syntax:
substring(String, Start, End)
substr(String, Start, Length)

WHERE
  • substring or substr in lowercase initiates the function
  • String is a single (not an array) Text element
  • Start the character number you wish to start on – zero based
  • End is the character number you wish to end on – zero based
  • Length is the desired number of characters and is not required

Things to keep in mind when constructing this function:

  1. The key element of the function is case sensitive and must be entered in all lower case
  2. The String element is required
  3. The Start element is required and must be a number
  4. The End element is required and must be a number
  5. The Length element is not required but must be a number
  6. If Length is left out, Ninox will return as many characters after the start as exist in the remainder of the string
  7. The result of the formula is a string
  8. You will receive an error in the formula if the input is not a Text element

Syntax Examples:
substring(“Nioxus is great!”, 0, 6) = “Nioxus”
substr(“Nioxus is great!”, 7) = “is great!”

Real World Solution:
Sometimes you want a field in each record to have exactly the same number of characters. Just use the lpad() function and you’ll get exactly what you want. Say you have a Category field that you’d like to pad to 15 characters.

Here’s the code:

lpad(Category, 15, “” )

The result would look like this:

Revision: 2
Last modified: 2019/11/03

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Please do not use this for support questions.
For customer support, please contact us here.

Post Comment