[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

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
0
jose11au
Asked:
jose11au
2 Solutions
 
OP_ZaharinCommented:
- 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
 
ajexpertCommented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now