Is there a saved search function that will return the number of occurrences that a substring appears within a string? For example, if my field value is: 'value1,value2,value3,value4', I want to know how many commas are in that string.

Hey, can’t you create a formula to count the length of the string, then substract the length of the sting without the selected character ?

Thanks. Your first suggestion sounds interesting and I'll give that a shot. As for INSTR, that seems to return the location of the first occurrence, or the first occurrence after the start index if specified.

I imagine there has to be an easier way but if you can get the string length and then compare that to the string length after applying a replace that replaces the comma with nothing that would be one approach. (Makes me cringe thinking about it though!)

Me too, but sounds like it'll work

I think you might be able to do this with a REGEXP too but really not my forte. There seems to be talk on StackOverflow if you Google your issue and include Oracle SQL which could allude to the format of the function.

@Mike Robbins I recently did something similar when I needed to find results where there were lots of

<br/>

in a column.

You could also try:

REGEXP_COUNT/*comment*/({fieldname}, ',')

Also look at: https://stackoverflow.com/questions/8169471/how-to-count-the-number-of-occurrences-of-a-character-in-an-oracle-varchar-value it was the first result searching for:

oracle sql number of occurrences in a string

I use this page for documentation of Oracle functions, and

REGEXP_COUNT

was under Numeric/Math functions so I missed it. I was looking under the String function. This is the right answer. Thank-you!!

Is REGEXP_COUNT working in netsuite ?  I tried that and it returned an error.

what was the error?

Not really returning an error. The column was not appearing at all for the formula with Regexp_Count in it. Regex_count is not listed in the available functions in the formula building form.

And........I'll backtrack my statement. </embarrased> I posted without testing. As LRG reported, that column does not even appear in the results when using

regexp_count

. Ultimately, I used the option to compare the length of the original string with the length of the string without the character in question.

Yes, I encountered the issue as well, where the column would not even show up. On a whim I added

/**comment**/

to the statement and that made it show up. I guess NetSuite detects it as an aggregate function, when it really isn’t.

I wanted to ask why you put that between the function name and the arguments. Thanks for that! Do you remember where you discovered this trick?

Works like a charm with that workaround. Really weird flex from oracle :s Or it might be a hack to get disabled functions from the oracle DB engine...

I remembered the trick from a post by Marty Zigman. It was for something different, but also was for working with aggregate functions. I also think it is some kind of hack to get around disabled functions, since

COUNT()

is an aggregate function, Netsuite might be thinking

REGEXP_COUNT()

is also an aggregate function. Or maybe it can be used as one, I haven’t checked.