Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

checking for non-numeric and numeric values in substr

Posted on 2012-12-26
3
Medium Priority
?
616 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
[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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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