Clever Geek Handbook

Netsuite SQL Expressions

I need to use Netsute SQL expressions in a custom field. There are certain criteria to be followed in the field:

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

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

  • Also, if the cell contains a forward slash '/', include and also the forward slash.

Here are 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 a batch script, but I don't have access to it, so I have to try to get it to work with SQL and formulas.

+3


source to share


1 answer


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/]

must match anything that is not a capital letter or a forward slash. This will then be replaced with an empty string, i.e. removed from the column.

+2


source






More articles:


All Articles