Q
Problem solve Get help with specific problems with your technologies, process and projects.

Help creating a specific query

I need to make a query with these conditions:

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'

 

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close