INSPECT statement to findout if a certain character is present in a given string (and how many)Planned
I would be helped with a function to inspect a string on the presence of a character or substring. Also if I can inspect the string it would be helpfull to have the option to Tally the number of occurrences.
I would like to know if the string 'bc' is present :
INSPECT('abcdefgh', 'bc') -> true (Boolean)
INSPECT_TALLY ('abcdefgh', 'bc') - 1 (integer)
In your case I would create two procedures.
The first procedure checks if a string is present.
Create virtual procedure custom_function.inspect(IN_String string,IN_Checkstring string)
contains boolean) as
It checks if the length of the original string ("IN_String") is the same as the String minus the to-be-removed-string("IN_CheckString"). If this is the case, the difference is greater than 0 -> True.
For the second function it is nearly the same :
Create virtual procedure custom_function.inspect_tally(IN_String string,IN_Checkstring string)
counts integer) as
If first calculates the difference between the original string and the replaced string, then it divides the result by the length of IN_Checkstring, e.g.
Select select (length('abcdefbc')-Length(Replace('abcdefbc','bc','')))/
(8 - 6) / 2
I suggest to create a own virtual schema called Custom_functions (or whatever works for you) and create the procedure in there.
I have already proposed such a solution however this kind of functions can be usefull for everyone.
Instead of creating our own custom functions it would be better if this would be facilitated by DataVirtuality.
For now your proposal can be used as a workaround. The function will not be pushed down to the source. With a native function maybe you can look for a possibility to have it pushed down towards the source.
in addition to my previous comment, right now we don't have an equivalent function for INSPECT_TALLY -- added already to the wishlist -- but regarding the INSPECT function, you could use LOCATE which is already implemented in Data Virtuality.
LOCATE returns an integer (the position of the substring or 0 if the substring is not present) instead of a boolean as you suggested with INSPECT function, but you can easily adapt your queries accordingly. LOCATE is also pushed down to the supported data sources.
Do you think that LOCATE can suit your needs?
Please sign in to leave a comment.