NetSuite Tips & Tricks: Removing/Adding spaces to a string using REPLACE and SUBSTR

Today we have another guest post from one of our Senior Consultants, Casey Andrews.  If Casey’s name looks familiar to you, there is good reason for that! Casey is one of our top NetSuite Tips & Tricks contributors and has a ton of useful NetSuite support advice to share.  Today’s topic: data field manipulation.

We have all run into the situation where a client would like to change the way data in a field is presented. There are several reasons for this, such as aesthetics, or using the data for a specified CSV import.

REPLACE and SUBSTR Definition

First, I will assume that you have no idea what REPLACE and SUBSTR actually do and give a brief definition of each.

REPLACE is a function that will replace a sequence of characters in a string with another set of characters. For example -

REPLACE('123123TVG', '123');
Returned Result: 'TVG'

SUBSTR (read as 'substring') allows you to extract a substring from a string. For example - 
SUBSTR('123123TVG', 7 (the starting position in the string), 3 (the number of characters that you would like to extract));
Returned Result: 'TVG'

Using these two functions in conjunction with one another allows you to replace, and then rebuild a desired string how you would like it.

REPLACE and SUBSTR Use Case:

We have a client that wanted to make sure that the UK Postal Codes that were being entered in the system adhered to a specific format. Some of the data had the correct format, but some did not. In this case, we needed to ensure that there was a space before the last three digits of the postal code. Sample data looked like this -

As you can see, some of the data has the space before the last three digits, but other data does not. We can't assume that the field has the correct placement of the space, so we will need to use REPLACE to remove it altogether. We also need to account for the different lengths of the strings to ensure that we are inserting the space in the correct place. We will do this with the following formula - 

First we will remove the space -

REPLACE({zipcode}, ' ');

Once we ensure that function delivers the desired result, we need to get the length of the string to find out where to insert the space using something like this -

LENGTH(REPLACE({zipcode}, ' '));

Now we have the information we need in order to insert the new space. In the case that the string has a Length of 5, we know that we will need to insert the space between the 2nd and 3rd character of the string. We can accomplish this by using the SUBSTR function that I mentioned earlier and using the double pipes to concatenate a space between the two parts of the string. It should look like this - 

CASE WHEN LENGTH(REPLACE({zipcode}, ' ')) = '5' THEN SUBSTR(REPLACE({zipcode}, ' '),1,2) || ' ' || SUBSTR(REPLACE({zipcode}, ' '),3,3) END

Using the 5 character example above, the first SUBSTR will return 'W7' and the second SUBSTR will return '3H6'. By concatenating the two we get the desired result which is 'W7 3H6'.

The full formula is below for your reference.

If you are reading Casey's post and thinking, "I wish I had that guy on my team..." - consider it done! Working with The Vested Group's NetSuite Support practices is a snap. Click below to contact us today and we can get the ball rolling immediately.