Solved

checking for non-numeric and numeric values in substr

Posted on 2012-12-26

Oracle Database

2 solutions

611 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

Comment

Question by:jose11au

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

3 Comments

LVL 23

Accepted Solution

by:OP_Zaharin

OP_Zaharin earned 300 total points

ID: 387225272012-12-26

- 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

LVL 14

Assisted Solution

by:ajexpert

ajexpert earned 200 total points

ID: 387225972012-12-26

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

Author Comment

by:jose11au

ID: 387226412012-12-26

Thank so much guys.

Featured Post

Question has a verified solution.

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

Resolving Oracle Object Names and Synonym Chains

Article by: sdstuber

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…

Oracle Database

The strange case of the insufficient privileges error when creating a materialized view using execute immediate inside a PL/SQL procedure.

Article by: Javier

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…

Oracle Database

Executing a SQL Script from a Unix Shell Script and Passing Parameters

Video by: Steve

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.