functions to count number of commas in a cell

dataservices job design
1

I need to build a looping structure with the loop counter based upon the number of commas in an input cell.

Trying different substring functions (and combinations of) to get a count of commas in a cell, and not having much luck.

I thought i could just extract the commas, then do a length on the output which will give me the loop counter.
So far I cannot extract the commas, getting the same non-comparison error.

Part of the problem seems to me trying to specify a comma
Do I have to escape the comma in the string example?

For instance,
match_pattern(Query_2.SNP_PLANNER, ‘,’)

Get this error:
[Query:Query_3]
Invalid WHERE clause. Additional information: <Non-comparison expression < match_pattern(Query_2.SNP_PLANNER, ‘,’)

is not allowed. (BODI-1111313)>. (BODI-1111078)

Can anyone point me to the correct syntax to count the numbers of commas in a cell?

Thanks much in advance,
Jack

janderson021 (BOB member since 2010-05-05)

2

Hi,

One way to count the number of commas in a string is to use the length and replace_substr functions, ie

length(str) - length(replace_substr(str, ‘,’, ‘’))

Using a function such as match_pattern in a query where clause requires a comparison on the function’s return value, e.g 1=match_pattern(…)

somebodi :australia: (BOB member since 2010-08-04)

3

they really need to expand the built ins. :frowning:

$curLoc 	= index($inputString,$searchChar,1);
$counter 	= 0;
while ($curLoc > 0)
	begin
		$counter 	= $counter + 1;
		$curLoc 	= index($inputString,$searchChar,$curLoc + 1);
	end
return($counter);

jlynn73 :us: (BOB member since 2009-10-27)

4

Hi,

One way to count the number of commas in a string is to use the length and replace_substr functions, ie

length(str) - length(replace_substr(str, ‘,’, ‘’))

Using a function such as match_pattern in a query where clause requires a comparison on the function’s return value, e.g 1=match_pattern(…)

Thank-you very much!
That worked exactly as I needed.

janderson021 (BOB member since 2010-05-05)

5

Thanks alot, I appreciate the help!

janderson021 (BOB member since 2010-05-05)