Vertica Functions - Using arguments

kamil

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?

Jim_Knicely

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)

kamil

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_Knicely

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.

kamil

@Jim: yeah! Thats it. Thank you!