INSPECT statement to findout if a certain character is present in a given string (and how many)

Planned

Follow

0

Don Seur

• May 14, 2019 05:35

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.

eg.

String 'abcdefgh'

I would like to know if the string 'bc' is present :

INSPECT('abcdefgh', 'bc') -> true (Boolean)

INSPECT_TALLY ('abcdefgh', 'bc') - 1 (integer)

• 0

  Comment actions Permalink

  

  Jonas Seemann

  • May 14, 2019 07:03

  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)
  returns(
  contains boolean) as 
  begin
  select length(IN_String)-Length(Replace(IN_String,IN_Checkstring,''))>0;
  end
  ;;
  
  call views.inspect('abcdefgh','bc');
  
  Returns "true".

  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)
  returns(
  counts integer) as 
  begin
  select (length(IN_String)-Length(Replace(IN_String,IN_Checkstring,'')))/
  length(IN_Checkstring);
  end
  ;;
  
  

  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.

  call custom_function.inspect_tally('abcdefgh','bc');;
  
  Select select (length('abcdefbc')-Length(Replace('abcdefbc','bc','')))/
  length('bc')
  
  (8 - 6) / 2 
  
  =2/2
  
   = 1

   

  I suggest to create a own virtual schema called Custom_functions (or whatever works for you) and create the procedure in there. 

   

  BR,

  Jonas

• 0

  Comment actions Permalink

  

  Don Seur

  • May 14, 2019 07:16

  Hi Jonas

  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.

   

  Best Regards

  Don

• 0

  Comment actions Permalink

  

  Salvatore Raunich

  • May 15, 2019 18:12
  • Edited

  Thanks Don for reporting this feature request and also to you, Jonas, for proposing a solution.

  Indeed, a built-in function might be rewritten and pushed down to the underlying data source, if supported.

  I added these two functions to the customer wishlist.

   

  Best

  Salvatore

• 0

  Comment actions Permalink

  

  Salvatore Raunich

  • May 15, 2019 20:15

  Hi Don,

  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?

  Best

  Salvatore

Please sign in to leave a comment.