a. The field code has these values:
'CC123456', 'CC12FG6', 'CC34236', 'CC7HGT2', 'CJ322999'...
b. I need a query that selects all the fields of the table that have in the field code: Begin with 'CC' and contain in the other part only NUMBERS. For example, I need the registers with the code field 'CC123456', 'CC34236'
First let us create a function that determines whether a string is a number or not.
create or replace function is_number(v_instr IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2(10) := 'FALSE'; v_temp NUMBER; BEGIN BEGIN SELECT TO_NUMBER(v_instr) INTO v_temp FROM DUAL; retval := 'TRUE'; EXCEPTION WHEN OTHERS THEN null; END; RETURN retval; END; /
Now, you can run a query against your table as follows using the function and it will return the desired result.
SELECT field_code FROM
WHERE SUBSTR(field_code,1,2) = 'CC' And IS_NUMBER(field_code,3) = 'TRUE'