How to count particular characters in a string?

[VolkerBarth]

Given the rich set of string functions in SQL Anywhere, is there one I have overlooked to count how often a particular character appears in a string?

As a workaround, I have compared the length of the original string to that when the particular character is replaced by an empty string, such as

begin
   declare strTest varchar(255) = 'Hello World!';
   declare chTest varchar(1) = 'l';
   select length(strTest) as len1, length(replace(strTest, chTest, '')) as len2,
      len1 - len2 as countCh
    -- return 12, 9, 3
end;

(Yes, I'm ignoring multi-byte chars here...)

Is there a builtin or other-wise more conventient way to do so with v16 and above?

[Vlad · Accepted solution]

I am curious, is this function really vital for you? I guess, you found your solution in SO: https://stackoverflow.com/questions/9789225/number-of-times-a-particular-character-appears-in-a-stri... and I personally don't see anything bad in it.
I have also checked different languages as well, and... well... people don't have built-in functions for this operation. That is why none of standard C-lib functions is mapped to SQL, because it doesn't exist 🙂

[VolkerBarth · Accepted solution]

I am curious, is this function really vital for you?

No, I was just asking whether there is a "more direct" way.

Thanks for the pointer, and no, I did not check on SO for this (simply because it's usually not worthwhile for SQL Anywhere questions IMHO, in contrast to MS SQL and other DBMS), but apparently my workaround was not too original:) - I was not aware there is no standard C-lib to do so, I might be too focussed on C++ with a standard like std::count().

[Vlad · Accepted solution]

If I am not mistaken std::count() is the algorithm, not a simple function. But in this world I have noticed that many languages & DB systems map C-libs to their "native" (or built-in) functions. And many errors, unexpected things, multi-byte support etc come from this simple implementation. That is quite easy to do - give a user a way to replace characters, write loops and so on, and he will implement algorithms by his own.

So I guess that is why you shouldn't expect any high-level abstractions from the declarative language such as SQL 🙂

I am not strong in SQL, but e.g. I didn't find a way to split the string into records, group them and count the frequency. I guess this will be the straight-forward solution for your task in SQL-friendly way.

[VolkerBarth]

OK, an attempt to count via splitting/tokenizing (based on Vlad's very helpful suggestion) would be the following:

begin
   declare strTest varchar(255) = 'Hello World!';
   declare chTest varchar(1) = 'l';
   select max(line_num) - 1 as CountCh
      from sa_split_list(strTest, chTest);
end;

Does also work well (with rather short strings, as in my case) - even better than expected because I was suspicious about the handling of "doubled characters" like in 'Hello' but the are treated as two separate delimiters...

[justin_willey]

very neat!

[Breck_Carter]

Easy to understand! ... and it even works for chTest = '!' (last character match).

IMO substituting select COUNT(*) - 1 AS CountCh makes it the easiest to understand, with len/replace second, then RowGenerator.

[Breck_Carter]

And the performance winner is... sa_split_list (when compared with sa_rowgenerator)

CREATE VARIABLE s LONG VARCHAR;
SET s = xp_read_file ( 'C:\\\\projects\\\\foxhound5\\\\$$$ DIARY 5.TXT' );

select count(*) 
  from ( select substr(s,row_num,1) as dumm from sa_rowgenerator(1,len(s)) where dumm = 'e' ) as x;

count()
215034

V16
Execution time: 2.815 seconds
Execution time: 2.611 seconds
Execution time: 2.674 seconds
Execution time: 2.578 seconds

V17
3.2 sec
3.1 sec
3.17 sec
3.24 sec


select count(*) - 1
      from sa_split_list(s, 'e' );

count()-1
215034

V16
Execution time: 1.97 seconds
Execution time: 0.715 seconds
Execution time: 0.984 seconds
Execution time: 0.596 seconds

V17
703 ms
701 ms
718 ms
688 ms

[former_SQLA_member1694950]

// lets say you want to count 'e' from 'Hello World'

select substr('Hello World',row_num,1) as dumm from sa_rowgenerator(1,len('Hello World')) where dumm = 'e'

[VolkerBarth]

I guess that is missing an enclosing query to do the real counting, such as

begin
   declare strTest varchar(255) = 'Hello World!';
   declare chTest varchar(1) = 'l';
   select count(*) as CountCh
   from (select substr(strTest, row_num, 1) as ch
         from sa_rowgenerator(1, len(strTest)) where ch = chTest) DT
end;

Besides that, an interesting approach!

[former_SQLA_member1694950]

yaa left the rest purpose fully to figure out on your own... 😛