Format a String with Decimal Point using REGEXP_REPLACE [message #669615] |
Wed, 02 May 2018 19:37 |
|
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
|
|
|
|
|