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