Home » SQL & PL/SQL » SQL & PL/SQL » Separate Alpha-Numeric Column into Alpha and Number
Separate Alpha-Numeric Column into Alpha and Number [message #239376] Tue, 22 May 2007 03:45 Go to next message
skkazmi
Messages: 44
Registered: April 2006
Member
Hi All,

I m using Oracle 9i database, i facing problem to separate alpha-numric column data into alpha n number. is their any build-in function in oracle 9i database to convert this column data separatly.

Khurram Kazmi
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239390 is a reply to message #239376] Tue, 22 May 2007 04:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a general response, try the REPLACE and TRANSLATE functions.
If you would be so kind as to give us more details. and possibly an example, then that would help us give you a more detailed response.
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239403 is a reply to message #239390] Tue, 22 May 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I should add SUBSTR, INSTR, REGEXP%...

Regards
Michel
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239418 is a reply to message #239376] Tue, 22 May 2007 05:34 Go to previous messageGo to next message
skkazmi
Messages: 44
Registered: April 2006
Member
i want to separate Data

e.g:= n:='hjerweSDFwa02389hjwe98'

into Alpha='hjerweSDFwahjwe' Number='0238998'

by using built-in function if any in Oracle 9i Database

Khurram Kazmi
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239423 is a reply to message #239418] Tue, 22 May 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's an easy one using TRANSLATE.
Try it and post if you are blocked.

Regards
Michel
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239425 is a reply to message #239418] Tue, 22 May 2007 06:16 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read the oracle Documentation

Elaborate

Ansluten till:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

omwb_user@UTV2> SELECT TRANSLATE(UPPER('hjerweSDFwa02389hjwe98'),
  2     'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ') 
  3     "Example"
  4       FROM DUAL;

Example
--------
 0238998
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239429 is a reply to message #239376] Tue, 22 May 2007 07:07 Go to previous messageGo to next message
skkazmi
Messages: 44
Registered: April 2006
Member
Thks man i will found the solution

DECLARE
V VARCHAR2(1000):='KHU534ERE0923';
C VARCHAR2(1000);
N VARCHAR2(1000);
BEGIN
SELECT Translate(V,'0123456789',' ') C,
Translate(V,Translate(V,'0123456789',' '),' ') N
INTO C,N
FROM DUAL;
Dbms_Output.PUT_LINE('CHAR='||C||' ,NUM='||N);
END;

Thks again,

Khurram Kazmi
Re: Separate Alpha-Numeric Column into Alpha and Number [message #239431 is a reply to message #239429] Tue, 22 May 2007 07:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you're on the right lines at least.
You can loose the SELECT entirely, which will improve performance quite a bit.
Previous Topic: SQL query to view ALL logged on users
Next Topic: Out mode in functions
Goto Forum:
  


Current Time: Sat Dec 10 12:56:30 CST 2016

Total time taken to generate the page: 0.04446 seconds