TRIM or REPLACE in Netsuite Saved Search

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

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