Vertica Functions - Using arguments

Comments

• Jim_Knicely - Select Field - Administrator

  February 2019

  Yes, function arguments can be used as input to the REGEXP_REPLACE function.

  Example:

  dbadmin=> create or replace function fname(x varchar2, y varchar2) return varchar2
  dbadmin-> as begin
  dbadmin-> return REGEXP_REPLACE(x, '\w+thy', y);
  dbadmin-> end;
  CREATE FUNCTION
  
  dbadmin=> SELECT fname('healthy, wealthy, and wise', 'something');
               fname
  --------------------------------
   something, something, and wise
  (1 row)
  

  0
• kamil ✭

  February 2019

  Ok,

  Your example is showing that i can use it as a string and replacement:
  REGEXP_REPLACE( string, target [, replacement [, position [, occurrence ... [, regexp_modifiers ] ] ] ] )

  but the thing is if arguments can be passed inside target option:

  return regexp_replace(name, E'.country:(.?)(?=\n).*', E'\1', 1, 0, 'n');

  so:

  name = string
  target = E'.country:(.?)(?=\n).*'
  replacement = E'\1'
  position = 1
  occurence = 0
  regexp_modifier = 'n'

  So can i pass country argument (e.g. US) and gather result like:
  return regexp_replace(name, E'.US:(.?)(?=\n).*', E'\1', 1, 0, 'n');

  0
• Jim_Knicely - Select Field - Administrator

  February 2019

  Hi,

  See if you can modify how you are passing the data to the REGEXP_REPLACE function.

  Example:

  (Using a simpler simpler case)

  First, verify that the REGEXP_REPLACE works with the simpler case. Here I replace E'US\n' with a zero length string (i.e. ''):

  dbadmin=> select regexp_replace(E'US\n', E'US\n');
   regexp_replace
  ----------------
  
  (1 row)
  

  That works. But can I do it in a function?

  dbadmin=> create or replace function fname(name varchar2, country varchar2) return varchar2
  dbadmin-> as begin
  dbadmin-> return regexp_replace(name, E'country\n');
  dbadmin-> end;
  CREATE FUNCTION
  
  dbadmin=> SELECT fname(E'US\n', 'US');
   fname
  -------
   US
  
  (1 row)
  

  Not like that!

  But like this:

  dbadmin=> create or replace function fname(name varchar2, country varchar2) return varchar2
  dbadmin-> as begin
  dbadmin->   return regexp_replace(name, country || E'\n');
  dbadmin-> end;
  CREATE FUNCTION
  
  dbadmin=> SELECT fname(E'US\n', 'US');
   fname
  -------
  
  (1 row)
  

  Make sense?

  @kamil - I think this is what you are after.

  1
• kamil ✭

  February 2019

  @Jim: yeah! Thats it. Thank you!

  0