CODEWITHSUNDEEP
netsuitesaved-searches
J Ban
J Ban

Reputation: 3

Netsuite saved Search Formula Trimming a Word

I'm trying to get the number next to the word. For example "1bot 10box 100cs" i have another field in netsuite which is "BOX". What i need is the number next to the word box which is 10 in my example.

I already tried Ltrim and Rtrim

regexp_substr((REPLACE((REPLACE({unitstype}, ' ', '-')),{transaction.unit},'^')), '[^-]+')

I expect the output will be number next to a word that i searched.

example 1. field A is equal to BOX and field B is equal to "1pc 10flp 100box 1000cs" the result should be 100 result = 100

Upvotes: 0

Views: 3069

Answers (1)

Krypton
Krypton

Reputation: 5231

See example below using the inputs you provided:

REGEXP_SUBSTR('1pc 10flp 100box 1000cs', '(\d+)BOX',1,1,'i',1)

Result = 100.

Assuming that field A is {unitstype} and field B is {transaction.unit} from your example, this would become:

REGEXP_SUBSTR({transaction.unit}, '(\d+)' || {unitstype},1,1,'i',1)

Explanation:

REGEXP_SUBSTR returns a sub-string of a given source string, defined by a regular expression.

1st parameter: source string.

2nd parameter: regular expression match pattern.

3rd parameter: position to begin search. Default is 1, but explicitly set here because we want to use a later parameter.

4th parameter: which occurrence of the matching pattern to return. Again, default is 1, explicitly set here because we want to use later parameters.

5th parameter: a match parameter that lets you change the default matching behavior of the function. In this case I've used 'i' to specify case-insensitive matching, as you have uppercase 'BOX' in one field and lowercase in the other.

6th parameter: specifies which capture group from the regex that you want to return. In this case you want the digits immediately before the units type, so you can match the digits and unit together, but use parentheses around the digits to define that as a capture group, which you can then return using this parameter.

Upvotes: 1

Related Questions