TRIM or REPLACE in Netsuite Saved Search

Question

I've looked at lots of examples for TRIM and REPLACE on the internet and for some reason I keep getting errors when I try.

I need to strip suffixes from my Netsuite item record names in a saved item search. There are three possible suffixes: -T, -D, -S. So I need to turn 24335-D into 24335, and 24335-S into 24335, and 24335-T into 24335.

Here's what I've tried and the errors I get:

Can you help me please? Note: I can't assume a specific character length of the starting string.

Use case: We already have a field on item records called Nickname with the suffixes stripped. But I've ran into cases where Nickname is incorrect compared to Name. Ex: Name is 24335-D but Nickname is 24331-D. I'm trying to build a saved search alert that tells me any time the Nickname does not equal suffix-stripped Name.

PS: is there anywhere I can pay for quick a la carte Netsuite saved search questions like this? I feel bad relying on free technical internet advice but I greatly appreciate any help you can give me!

Answer 1

You are including too much SQL - a formulae is like a single result field expression not a full statement so no FROM or AS. There is another place to set the result column/field name. One option here is Regex_replace().

REGEXP_REPLACE({name},'\-[TDS]$', '')

Regex meaning:

\-    : a literal -
[TDS] : one of T D or S
$     : end of line/string

To compare fields a Formulae (Numeric) using a CASE statement can be useful as it makes it easy to compare the result to a number in a filter. A simple equal to 1 for example.

CASE WHEN {custitem_nickname} <> REGEXP_REPLACE({name},'\-[TDS]$', '') then 1 else 0 end

Answer 2

You are getting an error because TRIM can trim only one character : see oracle doc https://docs.oracle.com/javadb/10.8.3.0/ref/rreftrimfunc.html (last example).

So try using something like this

TRIM(TRAILING '-' FROM TRIM(TRAILING 'D' FROM {entityid}))

And always keep in mind that saved searches are running as Oracle SQL queries so Oracle SQL documentation can help you understand how to use the available functions.