Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

   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

(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?

asked 07 Oct '19, 03:26

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 07 Oct '19, 03:27

I am curious, is this function really vital for you? I guess, you found your solution in SO: 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 :)

permanent link

answered 07 Oct '19, 08:02

Vlad's gravatar image

accept rate: 16%

converted 08 Oct '19, 09:41

Volker%20Barth's gravatar image

Volker Barth

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().

(07 Oct '19, 08:49) Volker Barth
Replies hidden

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.

(07 Oct '19, 08:59) Vlad

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

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

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...

permanent link

answered 07 Oct '19, 09:23

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 08 Oct '19, 11:29

very neat!

(08 Oct '19, 04:08) Justin Willey

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.

(08 Oct '19, 10:32) Breck Carter

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

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;


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

3.2 sec
3.1 sec
3.17 sec
3.24 sec

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


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

703 ms
701 ms
718 ms
688 ms

(10 Oct '19, 09:54) Breck Carter

// 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'

permanent link

answered 08 Oct '19, 04:16

Dev's gravatar image

accept rate: 20%

edited 08 Oct '19, 04:16

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

   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

Besides that, an interesting approach!

(08 Oct '19, 09:40) Volker Barth

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

(09 Oct '19, 14:33) Dev
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 07 Oct '19, 03:26

question was seen: 2,060 times

last updated: 10 Oct '19, 09:56