?
Solved

Oracle 10g  select numbers from string

Posted on 2012-09-21
3
Medium Priority
?
730 Views
Last Modified: 2012-09-21
Experts,

From the example data below I need to separate the numeric from the alpha and return as two separate values

SELECT
MY_COLUMN
FROM TABLE1
WHERE ........

MY_COLUMN
200CS
17EACH
20240LBS
12PLT

Need to return
QTY      UOM
200      CS
17      EACH
20      LBS
12      PLT

Not sure where to begin on this
0
Comment
Question by:JDCam
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38421426
I have this function on my side for extract the number part :
create or replace
FUNCTION GetNumber2(txt_IN IN varchar2)
RETURN NUMBER
IS
	test NUMBER;
	res VARCHAR2(128);
BEGIN

BEGIN
  res := TRANSLATE ( txt_IN, '0123456789' , '          ');
  res := REPLACE( TRANSLATE ( txt_IN, res ,  ' ' ) , ' ', '');

  test := TO_NUMBER(res);

EXCEPTION
  WHEN VALUE_ERROR THEN test:=NULL;
END;

RETURN (test);

END GetNumber2;

Open in new window


your query would become:
select getnumber2(MY_COLUMN) QTY
   , replace(MY_COLUMN, TO_CHAR(getnumber2(MY_COLUMN) ), '')  UOM
  from TABLE1 

Open in new window

0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38421428
Try this:

select regexp_substr(MY_COLUMN,'^[0-9]+') QTY, regexp_substr(MY_COLUMN,'[^0-9]+$') UOM
from TABLE1;
0
 
LVL 1

Author Closing Comment

by:JDCam
ID: 38421468
Amazing!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

621 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