Formula for number of occurances of a character in a string?
How do you write a formula to count the nunber of times a specific character occurs in a string?
Login to post new content in forum or to comment on an existing forum post.
Need Help?Talk to UsIn the U.S. and Canada, call 1-800-328-1000 and ask for Sales. Elsewhere in the world, contact your local international office. |
Counting the number of characters in a string
The following view will prompt for the character that you want to count. Then it calculates the original number of characters in the string, changes the search characters to blanks, removes the blanks and subtracts the new string length from the old string length to determine the number of times the character appeared in the original string.
With Sequel installed, you should be able to copy and paste the following SQL statement to see how it works. Don't forget to manually defined the variable if you are going to try this.
SELECT UPPER(cname) NAME(cname2), "&&character" NAME(choice) COLHDG("Character" "To" "Search"),
WDATA(LENGTH(TRIM(stripx(cname2," ")))) NAME(len1),
WDATA(LENGTH(stripx(TRANSLATE(stripx(cname2," ")," ","&&character")," "))) NAME(len2),
len1-len2 NAME(COUNT) COLHDG("Number" "Of" "Occurences")
FROM sequelex/custmast
_____________________________________________________________________
Variable specifications:
Name Type Len
---- ---- ---
&CHARACTER EXPR 10 Prompt text . . . : Choose character to search for
Default value . . :
Integrity check . : check(uc)
Omit lead values :
Omit trail values :
Extended help . . :