Home » SQL & PL/SQL » SQL & PL/SQL » Format a String with Decimal Point using REGEXP_REPLACE ([Oracle Database 11G, UNIX Solaris])
Format a String with Decimal Point using REGEXP_REPLACE [message #669615] Wed, 02 May 2018 19:37 Go to next message
Betaroot
Messages: 4
Registered: June 2017
Junior Member
Hello there, this question can be very simple to solve. But at this moment i googled it and can't found any result that match with my current requirement.

This is the scenario:
1. There is a PLSQL Procedure that reads a Text File (using UTL_FILE.FOPEN). All its fine here.
2. There is a LOOP that reads every line and SUBSTR the line according the rules on a Layout Document. All its fine here.
3. We need to format the fields that we are reading, and we need to this "dinamically". If we need a NUMBER field, we send to a Function the precision of the NUMBER field, and must to return it with the required format.

For example, the Text field has '12457896' and we need to format it like '124578.96' adding the decimal point.

For this case we wrote this code:

create or replace PACKAGE BODY                                                                                                                                         PKG_TDB_GENTEXTFILE_DEF
AS
FUNCTION fncConvertNumber(pValue VARCHAR2, pPrecision NUMBER DEFAULT 0) 
RETURN NUMBER IS  
  vValue VARCHAR2(25) := pValue;
  vPrecision NUMBER := pPrecision;
  vDecimalNumber NUMBER;
  vNewNumber NUMBER;  
  --vExpression VARCHAR2 (100);
BEGIN
  EXECUTE IMMEDIATE('ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''. ''');  
  --SLOG_PRO(vMenu,vPrograma,vOpcion,'H','INICIO fnc_getParamFileName');  

   IF vPrecision > 0 THEN   
    dbms_output.put_line('Precision greater than 0');       
    vDecimalNumber := REGEXP_REPLACE(vValue,'([[:alnum:]]{'||vPrecision||'})','\1.');
    dbms_output.put_line(vDecimalNumber);
    vNewNumber := vDecimalNumber;
    RETURN vNewNumber;
   ELSE 
    vNewNumber := TO_NUMBER(vValue);
    RETURN vNewNumber;
   END IF;
  --SLOG_PRO(vMenu,vPrograma,vOpcion,'H','FIN fnc_getParamFileName');   
EXCEPTION
  WHEN VALUE_ERROR THEN
   RETURN -1;  
  WHEN OTHERS THEN
    --SLOG_PRO(vMenu,vPrograma,vOpcion,'E','OTHERS:' || SQLERRM);
    RETURN -1;
END;
END PKG_TDB_GENTEXTFILE_DEF;

And we run this Anonymous Block to try it:
DECLARE
  vStringValue VARCHAR2(25):= '20'; --Try when no need to decimal
  vStringDecimalValue VARCHAR2(25) := '2014021412123'; --When we need Decimals
  vPrecissionValue NUMBER := 11; --We must to Add the real precission, from now is Field Length - Precision
  vReturnValue NUMBER;
  vReturnDecimalValue NUMBER;
BEGIN  
  vReturnValue := TAR.PKG_TDB_GENTEXTFILE_DEF.fncConvertNumber(vStringValue);
  vReturnDecimalValue := TAR.PKG_TDB_GENTEXTFILE_DEF.fncConvertNumber(vStringDecimalValue,vPrecissionValue);
  dbms_output.put_line(vReturnValue);
  dbms_output.put_line(vReturnDecimalValue);
END;

The problem is that the regexp doesnt work if the precision is smaller, because it breaks on the precision. So we need just to have a right regular expression that maybe evaluate from the end of the value to the required precision (probably 2 decimals). Like this:

Value: 12345
Precision: 2

Result: 123.45

¿Any ideas?

[Updated on: Wed, 02 May 2018 19:39]

Report message to a moderator

Re: Format a String with Decimal Point using REGEXP_REPLACE [message #669618 is a reply to message #669615] Thu, 03 May 2018 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace(to_char(&number),'(.{&precision})$','.\1') from dual;
Enter value for number: 12345
Enter value for precision: 2
REGEXP
------
123.45

1 row selected.

SQL> /
Enter value for number: 12457896
Enter value for precision: 4
REGEXP_RE
---------
1245.7896

1 row selected.

Quote:
This is the scenario:
1. There is a PLSQL Procedure that reads a Text File (using UTL_FILE.FOPEN). All its fine here.
2. There is a LOOP that reads every line and SUBSTR the line according the rules on a Layout Document. All its fine here.
3. We need to format the fields that we are reading, and we need to this "dinamically". If we need a NUMBER field, we send to a Function the precision of the NUMBER field, and must to return it with the required format.

For me, the whole stuff seems to be able to be done with an external table and a single query.

Re: Format a String with Decimal Point using REGEXP_REPLACE [message #669623 is a reply to message #669618] Thu, 03 May 2018 10:01 Go to previous message
Betaroot
Messages: 4
Registered: June 2017
Junior Member
Thank you Michel. This is what i need.

I appreciate your response and i´m agree with you, external tables is a better approach, but for the moment we need to use some of the old logic to "have a lesser impact to the app" (this is probably wrong, but is the requirement for now).
Previous Topic: SQL statement with averages
Next Topic: Main Stored procedure to execute multiple procedures
Goto Forum:
  


Current Time: Thu Sep 24 04:01:29 CDT 2020