?
Solved

How to check if contents of SUBSTR are numeric?

Posted on 2006-05-22
9
Medium Priority
?
1,261 Views
Last Modified: 2008-01-09
In a SELECT statement, I want to use a condition to select only records where the first 5 positions in a particular field are numeric, and might contain leading zeroes. In Hyperion Intelligence Designer, I have to do this in three steps after the query is processed:

1. In the Results section I have added a calculated column named P5 that performs the SUBSTR:
     Substr ( Project_Name, 1, 5 )

2. Then in the Results, I have added another calculated column named P5Num that checks to see if P5 is between 00000 and 99999:
     if ((P5 >=00000)  && (P5<=99999)) P5
     i.e., if the condition fails, nothing is written to P5Num; also there can be leading zeroes

3. And finally, a limit placed on P5Num in the Results section to show only those that are not null.

I have moved to another part of the company that will not be using Hyperion, and I need a better command of writing SQL statements myself (Sql*Plus). Can a WHERE clause be written that can pull only those rows where the first 5 characters of the text field Project_Name are numeric? Remember, the columns P5 and P5Num are not in the database tables - they are created by me in the Results section as additional fields to those pulled via the query.

Thanks in advance.
0
Comment
Question by:WileECoyote45305
9 Comments
 
LVL 8

Author Comment

by:WileECoyote45305
ID: 16737276
Additional comment: All 5 positions at the beginning of Project_Name must be numbers.

An example of a Project_Name that I would NOT want to get in my results is "2006 Expert Awards", because not all of the first 5 characters are numbers. Also unwanted would be something like "N3579 yabba dabba doo".

I would, however, want to get something like "01234 blah blah blah" or "29784 yakety yak".
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 1600 total points
ID: 16737299

Use TRANSLATE() function:

If translate(SUBSTR(Project_name,1,5),'~01234567890','~') is NULL Then Its numeric.
0
 
LVL 8

Author Comment

by:WileECoyote45305
ID: 16737568
So I could write it in the WHERE clause like this?
SELECT Project_Name FROM Projects WHERE TRANSLATE(SUBSTR(Project_Name,1,5),'~01234567890','~') IS NULL;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:paquicuba
ID: 16737612




In 10g you can use regular expressions, see below:

PAQUI@PROD > SELECT P5 FROM P5TAB;

P5
------------------------------
1234567890
2006 Expert Awards
N3579 yabba dabba doo
01234 blah blah blah
29784 yakety yak

Elapsed: 00:00:00.00
PAQUI@PROD > SELECT P5 FROM P5TAB WHERE REGEXP_LIKE(P5,'^[[:digit:]]{5}');

P5
------------------------------
1234567890
01234 blah blah blah
29784 yakety yak

Elapsed: 00:00:00.00
PAQUI@PROD > SELECT P5 FROM P5TAB WHERE REGEXP_LIKE(P5,'^[[:digit:]]{5}[[:space:]]');

P5
------------------------------
01234 blah blah blah
29784 yakety yak

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16738063

Yes, you can code as you posted -- or use regular expressions if you have 10g.

0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 400 total points
ID: 16738349
>>SELECT Project_Name FROM Projects WHERE TRANSLATE(SUBSTR(Project_Name,1,5),'~01234567890','~') IS NULL

You might revise it a little as (make sure you always get 5 leading characters)

SELECT Project_Name FROM Projects
WHERE length(SUBSTR(Project_Name,1,5)) = 5 AND TRANSLATE(SUBSTR(Project_Name,1,5),'~01234567890','~') IS NULL
0
 
LVL 5

Expert Comment

by:fmonroy
ID: 16740114
create or replace function is_number (string_in varchar2)
return boolean
is
val number;
begin
val := to_number (string_in);
return true;
exception
when others then return false;
end;
/
0
 
LVL 8

Author Comment

by:WileECoyote45305
ID: 16746284
Thanks much. Using just translate(SUBSTR(Project_name,1,5),'~01234567890','~') allowed a Project_Name of '1111' to come through, so then I added the bit about the length, and the '1111' project did not show up.

It even works in Hyperion, adding this as the SQL limit on the Project_Name field:

(Translate(Substr(Projects.Project_Name,1,5),'~01234567890','~') IS NULL) and (Length(Substr(Projects.Project_Name,1,5))=5)
0
 
LVL 8

Author Comment

by:WileECoyote45305
ID: 16746293
And we don't have 10g yet, so though the responses specifying 10g may be valid, I can't use them at this time.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question