Home » SQL & PL/SQL » SQL & PL/SQL » Numeric and alphabetic data operations
Numeric and alphabetic data operations [message #252085] Tue, 17 July 2007 15:51 Go to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
Hi,
I got a building_no field(varchar2) in a table.
It can contain different formats of data..ie, it could be either '38' or '38A' or a range of numbers like '38-42' or '15A-20A' etc.
I want to do different operations through a pl/sql code when the data is like '30-35' and when it is like '30A-35A'.
My questions are,
1)Is there a way to find out whether the data in the building_no field is a number range of data or else alphabetic range of data?
2)Is it possible to do numeric operations on data like 30A-35A by extracting numbers from it?I want to generalize the operations as it could be like 4A-4C,300C-315G etc.
I will explain more about it:
In the building_no field, I now have data 30A-35A.Now if I have data 34A coming into the table,I want to delete it(as 34A is coming in between 30A and 35A).
Please help me in solving these, its a bit urgent.If you can help, it will be great.
Thanks a lot.
Re: Numeric and alphabetic data operations [message #252087 is a reply to message #252085] Tue, 17 July 2007 16:01 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
First of all, nothing in this forum is considered urgent. If it is indeed an urgent problem then hire a consultant.

Secondly, it's probably doable, not pretty or neat, but with combinations of INSTR and SUBSTR you could probably get what you want/need.

Show us what you've tried so far.

[Updated on: Tue, 17 July 2007 16:04]

Report message to a moderator

Re: Numeric and alphabetic data operations [message #252092 is a reply to message #252085] Tue, 17 July 2007 16:25 Go to previous messageGo to next message
aviana
Messages: 101
Registered: July 2007
Senior Member
In order to try substr function inside to_number function, to extract the number part, you need to know the exact position where the alphabet start, isn't it?In my case, it could be either 30A or 300A or 3A so how can I extract number from it using substr function?

I am also trying to remove an alpha numeric number which comes in an overlapping range..say for example, how can I determine that 34A is in between 30A and 35A or 340A is in between 300A and 350A...It is the same column so there should be a general way out..
Re: Numeric and alphabetic data operations [message #252165 is a reply to message #252092] Wed, 18 July 2007 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
INSTR is there to find the '-' or letter.
Then it is just a matter of SUBSTR to get the numbers.

There is no programming difficulty to do this.
If you want to do it in pure SQL, it will surely depends on what you finally want to achieve.

Regards
Michel
Re: Numeric and alphabetic data operations [message #252829 is a reply to message #252092] Fri, 20 July 2007 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use:
SQL> with 
  2    original_data as (
  3      select '3' col from dual
  4      union all
  5      select '30-50' from dual
  6      union all
  7      select '300A-350A' from dual
  8    ),
  9    modified_data as (
 10      select translate(col,'0ABCDEFGHIJKLMNOPQRSTUVWXYZ','0') col
 11      from original_data
 12    )
 13  select decode(instr(col,'-'),
 14                0,to_number(col),
 15                to_number(substr(col,1,instr(col,'-')-1))) first,
 16         to_number(substr(col,instr(col,'-')+1)) last
 17  from modified_data
 18  /
     FIRST       LAST
---------- ----------
         3          3
        30         50
       300        350

3 rows selected.

Regards
Michel
Re: Numeric and alphabetic data operations [message #252840 is a reply to message #252829] Fri, 20 July 2007 04:06 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And for the extended case you private message me:
SQL> with 
  2    original_data as (
  3      select '35' col from dual
  4      union all
  5      select '300A' col from dual
  6      union all
  7      select '40' col from dual
  8      union all
  9      select '590A_600A' col from dual
 10      union all
 11      select '5C_9C' col from dual
 12      union all
 13      select '6/6A' col from dual
 14      union all
 15      select '260-287' col from dual
 16      union all
 17      select '89D' col from dual
 18    ),
 19    modified_data as (
 20      select col original_col, translate(col,'/_ABCDEFGHIJKLMNOPQRSTUVWXYZ','--') col
 21      from original_data
 22    )
 23  select original_col, 
 24         decode(instr(col,'-'),
 25                0,to_number(col),
 26                to_number(substr(col,1,instr(col,'-')-1))) first,
 27         to_number(substr(col,instr(col,'-')+1)) last
 28  from modified_data
 29  /
ORIGINAL_      FIRST       LAST
--------- ---------- ----------
35                35         35
300A             300        300
40                40         40
590A_600A        590        600
5C_9C              5          9
6/6A               6          6
260-287          260        287
89D               89         89

Regards
Michel
Previous Topic: 12-month range window count
Next Topic: How to attain DYNAMIC PL/SQL Table Structure Comparing and Copying?
Goto Forum:
  


Current Time: Thu Dec 08 05:52:16 CST 2016

Total time taken to generate the page: 0.11900 seconds