SQL & PL/SQL

Hi,

I have imported almost 6.5 milion rows into a table (using sql loader) from a flat file (which contains the NUL caracter (ASCII 0) - not space, not NULL). To make the load easier I set the datatype as VARCHAR2 for all columns. For columns with string data I used a TRIM(REPLACE(field,' ','')) to get rid of the NUL and it works.

But for the column that must be converted to numeric I am trying to use to_number () function and it fails because the numeric data is mixed with other characters.

To solve this problem I created this function :
"
CREATE OR REPLACE
FUNCTION string_to_number
(p_string_source IN VARCHAR2 ) RETURN VARCHAR2 IS v_output_string varchar2(150);


-- This function takes a string as parameter and has 2 outputs:
-- 1. If the source string cannot be converted to number throw 'Error' as the output message to identify the line with the issue
-- 2. A string that can be converted successfully as Number;
-- The necessity of this function came up after an import from a flat file where the resulted string contained strange characters shown as spaces

--check if '-' is exists and is on the first position or if in the source string exists more than one '.'
-- ASCII codes accepted in the string :
-- 45 '-' ; 46 '.' ; from 48 (0) to 57 (9)

BEGIN
DECLARE v_minus VARCHAR2(1);
v_dot INTEGER;
BEGIN
SELECT substr(p_string_source,1,1) INTO v_minus FROM dual;
SELECT instr(p_string_source,'.',1,2) INTO v_dot FROM dual;


--check if '-' is exists and is on the first position or if in the source string exists more than one '.'
-- ASCII codes accepted in the string :
-- 45 '-' ; 46 '.' ; from 48 (0) to 57 (9)

IF v_minus NOT IN ('-','.','0','1','2','3','4','5','6','7','8','9')

-- or there are two dots '.' in the string
OR v_dot <> 0
THEN v_output_string := 'Error';
ELSE
BEGIN

-- for every character of the string we'll check if it's a number to add it to the outcome string;
-- if it's not an accepted character it will be ignored
DECLARE v_length_source int := length(p_string_source);
v_counter int :=1;
v_add_in_number VARCHAR2(1);

BEGIN
FOR v_counter IN 1..v_length_source LOOP
BEGIN
SELECT SUBSTR(p_string_source,v_counter,1) into v_add_in_number from dual;
IF v_add_in_number IN ('-','.','0','1','2','3','4','5','6','7','8','9')
THEN v_output_string := v_output_string ||v_add_in_number;
END IF;
END;
END LOOP;
END;
-- in case the string is in format '.00034' we'll add a 0 in front of the string to be accepted as argument by TO_NUMBER function
IF v_minus = '.'
THEN v_output_string := '0'||v_output_string;
ELSE
BEGIN
v_output_string := v_output_string;
END;
END IF;
END;
END IF;

END;
RETURN v_output_string;
END;
"
The main idea is to check every string (the parameter will be the value from the Amount column) for permitted characters that compose a numeric value:
1. To begins with numeric, '-' or '.'
2. To have only one '.' (as a decimal separator);
3. To compare every character of the string with the permitted ones - the non-compliant will be rejected

This way the resulting string (v_output_string) will be successfully converted to number


I admit that I don't have much experience using PL/SQL that is why I am asking your help to optimize this function to improve its performance. Could you help me on this, please ?

TIA,

JohnP

Edited by: petresion on 04-Oct-2012 01:33