| Searching between alphanumeric ranges [message #372097] |
Wed, 10 January 2001 04:46  |
David Donaldson
Messages: 1
Registered: January 2001
|
Junior Member |
|
|
I'm trying to write a query that would allow me to search between an alphanumeric range. The database fields are held as varchar2 although some of the data may be straight numbers.
Because the fields are varchar2 a standard between does not always work eg between 2 and 20 would only bring back records 2 and 20!! Then there is the possibility that we woul have to search between an alphanumeric range..
Has anyone encountered anything similar?
|
|
|
|
| Re: Searching between alphanumeric ranges [message #372139 is a reply to message #372097] |
Fri, 12 January 2001 15:52  |
dapzoid
Messages: 1
Registered: January 2001
|
Junior Member |
|
|
If I understand your question correctly, you have a problem whereby numbers have been stored into a Varchar2 field.
Even if oracle (decides) to calculate or use the (between operator) the answers will be distorted.
Here is a solution to your problem I know you will be able to handle:
1)
A New TABLE(b) will have to be created from the old table(A) selecting all the other columns except the problematic col (varchar2)housing the numbers because what is intended now is to transfer the data in Varchar2 into a newly created number field.
Please note that you will have to keep the new table(B) created empty of data. This you can do by addind at the end of your statement when you are creating your new table (b), with where 1=2;
2)
Then you will have to write a procedure in a PL/SQL Block with the intention of converting the contents in the varchar2 field housings the numbers to the correct format.
PDL
For developing your procedure you can follow these steps;
1) Get a record from Table A(the old table)
2).Examine content of the faulty Field
3)Convert the content to the correct format
4)insert the record into (the new table b)
You will need an explicit cursor and the CURSOR FOR LOOP which will help in picking records automatically during INSERTION INTO THE NEW TABLE.
Now that you have created a new table with data in the right places you can now go ahead and do your calculations or query the database.
I hope this helps. However if all these is not too clear feel free to email me.
Cheers and goodluck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
