REGEXP_REPLACE function is not working

Hi , I am facing an issue while using an REGEXP_REPLACE function in order to remove the special characters of a value for a specified column. Below is the query we are using and the error Query : SELECT cstone_feed_key, cstone_last_updatetm, fin_card_prod_mkt, fin_card_prod_lob, fin_card_prod_cd, fin_merchant_toc_size, fin_client_segment, fin_netwrk_nuc_iss_org_id, fin_netwrk_nuc_acq_org_id,REGEXP_REPLACE(fin_merchant_mkt_toc_se10,'[^a-z0-9A-Z]',''), fin_merchant_mkt_toc_name, fin_merchant_mkt_toc_se10_market, fin_merchant_rgn_toc_se10_market, fin_merchant_rgn_toc_se10_name, fin_merchant_brand_toc_se10, fin_merchant_brand_toc_name, fin_client_id, fin_client_name, fin_tot_disc_billed_vol_usd, fin_tot_gr_disc_rev_usd, fin_tot_gr_disc_rev_local_curr_am, fin_tot_disc_billed_vol_local_curr_am, fin_tot_disc_billed_vol_local_curr_cd, fin_no_of_rocs, fin_merchant_industry, fin_merchant_construct, fin_odl_fin_report_month FROM fin_cust_prod_merchant_client_prft Error: Finished with error: Function 'regexp_replace' with arity 3 not found
user
14-05-2019 14:36:01 -0400

3 Answers

Hi, If you want to use a regular expression and replace it with some text, you can use the regexp function available in the Denodo. This function will work in a similar fashion you want. The definition of this function is regexp(field, regex, replacement). For example in your case, you can use regexp(fin_merchant_mkt_toc_se10,’[^a-z0-9A-Z]’,’’). If you want to find if the function exists in Denodo you can go to Help from the menu bar and click on the Functions list. This will open a window with all the available functions and its definition. Here is the link for text manipulation function you can refer for further details: https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions Hope this helps!
Denodo Team
16-05-2019 13:05:37 -0400
Hi , I even tried using REGEXP as you mentioned but we are facing the issues as below and we also tried REGEXP_LIKE as well but faced same issue.Can you let me know other alternative for this. query : Select regexp(fin_mkt_toc_mer_nm,’[^a-z0-9A-Z]’,’’) from fin_cust_prod_merchant_client_prft1 where fin_gmar_mkt_toc_se10 ='8048419741' Error: QUERY [VIRTUAL] [ERROR] QUERY[JDBCWRAPPER] [ERROR] QUERY[JDBCROUTE] [ERROR]:Received exception with message'unable to find mapping for function 'regexp'' Thanks.
user
16-05-2019 13:22:53 -0400
Hi, It seems that the REGEXP function is not there in your database or its mapping is not available so you’ll not be able to use them from denodo unless you apply them in denodo server instead of pushing them down. To do this you can go to the scalar function lists and remove it from the list so that this function won’t be delegated to the source. The scalar function list can be found in data source > configuration> source configuration. You can either uncheck the Delegate scalar function list or remove regexp from the list. Also, you can write a custom function for regexp_replace which has the same definition as the function in the data source and put that in the scalar function list. To write a custom function you can follow the below link https://community.denodo.com/docs/html/browse/7.0/vdp/developer/developing_extensions/developing_custom_functions/creating_custom_functions_with_annotations#developing-custom-functions-that-can-be-delegated-to-a-database Hope this helps!
Denodo Team
17-05-2019 21:19:37 -0400
You must sign in to add an answer. If you do not have an account, you can register here