Outsystems equivalent REGEXP_REPLACE Oracle function.

Outsystems equivalent REGEXP_REPLACE Oracle function.

  

Hi;

Is there is a Outsystems equivalent REGEXP_REPLACE Oracle function?

This on the SQL give me this Error:

Error in advanced query ConvertSSN in Savebuttonbottom in AgentDetail in MainFlow in AgentManagement (SELECT REGEXP_REPLACE (@DashSSN, '-') FROM DUAL  ): 'REGEXP_REPLACE' is not a recognized built-in function name.

I am using the CustomMasks and although I have KeepMask = False it saved the SSN with the Dash.

Edgar

Hi Rooster,


Is your database type oracle?

In advanced queries you are allowed to use anything available to you on your database, so that function should work if you are using an oracle database.


Edit: I'll also move this topic to a more appropriate forum section.

Regards,
João Rosado

João Rosado wrote:

Hi Rooster,


Is your database oracle?

In advanced queries you are allowed to use anything available to you on your database, so that function should work.


Regards,
João Rosado

Hi Joao;

Thanks for your quick response.

I have Oracle 11g.

It looks like Outsystems does not support Oracle function REGEXP_REPLACE. It does not work.

 It work with this SELECT REPLACE(@DashSSN,'-',' ') FROM DUAL

Regards,

Edgar(Rooster)

Like I said, It's not a question of if "OutSystems supports" or not. The contents of advanced queries are sent to the database almost "as is", apart from the tables and column names that are inside {} and [].

So any function in the query is send without any change from the OutSystems part.


Also "is not a recognized built-in function name" when searched in google appears in MSSQL pages, and not Oracle. Thats why I asked if your database was Oracle (and MSSQL does not have indeed the REGEXP_REPLACE function).


Also I'm curious in why does your query have a @DashSSN. Are you doing a query just to do a regexp/replace on a parameter you are passing? Why don't you just do it in server without going to the database at all?

Regards,
João Rosado

I have a screen that users input the Social Security number with the MaskEdit  ###-##-#### (555-55-5555). Although on the MaskEdit the option is set False on KeepMask, when saving the data ir is save 555-55-5555 and not 555555555.

So I created a Structure to convert the value from 555-55-5555 to 555555555 with the
SELECT REPLACE(@DashSSN,'-',' ') FROM DUAL function that @DashSSN is the input parameter on the SQL having the 555-55-5555 and convert to an OutputStruture field that I call ConvertSSN and pass that value 555555555 to the database.

Solution

Then why don't you just call the Replace() builtin function in an expression outside the query? It does the same work without having to go to the database and it's a lot faster.


Solution

You are right!. I am new in this rodeo. with only 3 months in Outsystems. Will try that. Thanks.

No worries, we are here to help.

My advice on this type of things is to avoiding doing work in the database that can be done outside of it. Remember that it's easier to scale performance on the server side than on the database.

Queries like yours without using any entities should be a red flag that something can be improved.


Regards,
João Rosado