Vertica Functions - Using arguments

kamilkamil Registered User

Guys,

Got some doubts about using functions. Below an example:

create or replace function fname(name varchar2, country varchar2) return varchar2
as begin
return regexp_replace(name, E'.country:(.?)(?=\n).*', E'\1', 1, 0, 'n');
end;

So, is it possible to use input argument country inside regexp_replace?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    
  • kamilkamil Registered User

    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');

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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.

  • kamilkamil Registered User

    @Jim: yeah! Thats it. Thank you!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file