NetSuite: Substring Formula in Saved Searches

If you want to get part of a string, you can use the SUBSTR() SQL function in a formula. The syntax is:

SUBSTR(string, start, length)

 

This is an example of a Customer Saved Search that would return the part of a customer's entitystatus proceeding the hyphen.

String: {entitystatus}

Start:  instr({entitystatus},'-')+1

• INSTR() is an SQL function that returns for the location of a specified character from a text. In this example, we are searching for the hyphen. INSTR() will return a number where are SUBSTR() will begin.
• Since INSTR() returns the location before the hyphen, we will need to add one after our INSTR() function.

Length: length({entitystatus})

• LENGTH() is an SQL function that returns the number of characters in a string. We can used this as the third parameter in our substring function to ensure we capture everything to the right of the hyphen.

Putting it all together, the formula is as follows:

substr({entitystatus},instr({entitystatus},'-')+1,length({entitystatus}))

Results tab:

Sample Results: