How to check if contents of SUBSTR are numeric?
Posted on 2006-05-22
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.