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? |
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-string and I personally don't see anything bad in it.
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: 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... 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
2
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
(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' 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!
(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
|