checking for non-numeric and numeric values in substr

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
jose11auAsked:
Who is Participating?
Advertise Here
 
OP_ZaharinConnect With a Mentor Commented:
- 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
 
ajexpertConnect With a Mentor Commented:
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
 
jose11auAuthor Commented:
Thank so much guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.