Search code examples
sqlformulanetsuitefreetext

Netsuite SQL Expressions

I need to use Netsute SQL expressions on a custom field. There are certain criteria that the field needs to follow;

  • If the cell contains only 1 (or none) capital letters, return the first 2 characters, capitalized.

  • If the cell contains 2 (or more) capital letters, return only these.

  • Also, If the cell contains a forward slash '/' then include the
    forward slash as well.

I will give some examples;

Light Blue
Dark Navy
Yellow
BlacK/ReD
blue check
WHite/NAvy/GreY
berry

should become

LB
DN
YE
BK/RD
BL
WH/NA/GY
BE

I know this might be easier to accomplish with suite script, but I don't have access to it, so I have to try and make it work with SQL and formulas.

Solution

  • You can try the following query:

    SELECT CASE WHEN LENGTH({col}) <= LENGTH(REGEXP_REPLACE({col}, '[A-Z]', '')) + 1
            THEN UPPER(SUBSTR({col}, 1, 2))
            ELSE REGEXP_REPLACE({col}, '[^A-Z/]', '')
       END AS output
FROM yourTable

    The regex pattern [^A-Z/] should match everything which is not a capital letter or forward slash. This then would be replaced with empty string, i.e. removed from the column.