Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

checking for non-numeric and numeric values in substr

Posted on 2012-12-26
3
Medium Priority
?
620 Views
Last Modified: 2012-12-26
Hi All,

I need your assistance. I have a simple select statement below but I am struggling with non-numeric and numeric values. I would like to check if characters 2 to 7 of 'VHT56789TYHBG' are numeric and if characters 2 to 7 of 'VHT56789TYHBG' are non-numeric and to remove 1st and last 2 characters of 'VHT56789TYHBG'.

Thanks in advance.




select
CASE
WHEN substr('VHT56789TYHBG',2,7) –how to check for numeric
THEN substr('VHT56789TYHBG',,8,2)
WHEN substr('VHT56789TYHBG',,2,7) –how to check for non-numeric
THEN substr('VHT56789TYHBG',)—then remove 1st and last 2 characters
ELSE
'VHT56789TYHBG',
END
from dual
0
Comment
Question by:jose11au
3 Comments
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 1200 total points
ID: 38722527
- the example is using LENGTH(), TRIM() and TRANSLATE() function that returns NULL if the given string is NUMERIC and value greater than 0 if it contain non-numeric. I changed the string to V1256789TYHBG to test for numeric and VHT56789TYHBG for non numeric:

select
CASE WHEN LENGTH(TRIM(TRANSLATE(SUBSTR('V1256789TYHBG',2,7), ' +-.0123456789',' '))) is NULL--–check for numeric if true.
  THEN substr('V1256789TYHBG',2,7)
ELSE -- if not numeric
 'V1256789TYHBG'
END results
from dual
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 800 total points
ID: 38722597
Other way is to write custom function (and you can call via SELECT statement) too

CREATE OR REPLACE FUNCTION chk_numeric (p_in_str IN VARCHAR2)
   RETURN VARCHAR2
AS
   v_ret_val   VARCHAR2 (100);

    p_new_str := SUBSTR (p_in_str, 2, 7);

   FUNCTION is_number (p_str IN VARCHAR2)
      RETURN VARCHAR2
   IS
      l_num       NUMBER;
      p_new_str   VARCHAR2 (4000);
   BEGIN
      


      l_num := TO_NUMBER (p_new_str);
      RETURN 'Y';
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 'N';
   END is_number;
BEGIN

   v_chk_str    := is_number(p_new_str);
   
   IF v_chk_str = 'N' THEN
        -- other logic...
        
        NULL;                
   END IF;
END chk_numeric;

Open in new window

0
 

Author Comment

by:jose11au
ID: 38722641
Thank so much guys.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

783 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