|
|
|
|
| Re: Seperate-Comma [message #291217 is a reply to message #291210] |
Thu, 03 January 2008 07:24   |
swamitvk
Messages: 14
Registered: November 2007
|
Junior Member |
|
|
Hi
i have stored numbers in a column that is defined as varchar.
The numbers contain comma, i want to remove that.
would plz help me?
I thank you in advance
|
|
|
|
|
|
| sql for migration [message #291222 is a reply to message #291210] |
Thu, 03 January 2008 07:39 |
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
|
Member |
|
|
Dear Expert,
I have changed our old datas entries into oracle software
i have stored all our amount datas in varchar datatype format
But i like to change that amount column from varchar datatype to number datatype,but it show some error due to ','
eg: Amount
------
32,98,000
3,78,000
67,000.00
1,98,000
how can i trim this values and covert into number format
Please help to overcome the issue.
- Thanx in advance.
|
|
|
|
|
|
| Re: Seperate-Comma (merged) [message #291544 is a reply to message #291210] |
Fri, 04 January 2008 15:22 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
You guys should really consider a design change. Also, don't visit ASKTOMHOME as Tom will slap you silly for putting a number in a varchar column (as he has done to several others).
There are a couple of real problems when you do this that you might have to eventually address:
1) as was indicated by other posts here, you can always use REPLACE to remove elements of a string. I won't show an example because if you can't figure out how to use REPLACE in SQL you need to get another job. In simple situations, this will allow you to convert your text string to number for calculation purposes.
2) once you get REPLACE to work, you still have to deal with the fact that you are using a varchar datatype to store what should be numbers, which means the database will allow you to store values that won't convert to a number no matter how many commas you remove. You will see this happen when you do your to_number either explicitly or implicitly, and get an INVALID_NUMBER error or PL/SQL NUMERIC OR VALUE ERROR. Since Oracle does not have any inbuilt to_number_return_null_on_error function, you will have to build it yourself. Not hard, but it does mean you now have to make sure everybody who selects the column as a number must use the function when they do it. I suppose you can get around that by using a view to mask the function use. Oh wait, this is getting more complicated that I wanted, lots of places for errors...
3) even if you get past #1 and #2, you still have to deal with language issues. Consider this number: 123,456.99 as vs. this number 123.456,99 (hmm...) these are the same number, just with different delimiters because of the country and/or NLS of origin. If you use a "dumb" comma remover, then in one case you will get the number one hundred twenty three thousand four hundred fifty six dollars and ninteynine cents, but in a different language you get one hundred twenty three dollars and fourty six cents (rounded to two places of course). That is a big difference eh? and all because the data you stored was taken in as a number on one database using one set of NLS parameters, as vs. taken in on a different origin database using a different set of NLS parameters. How do you fix that? Hmm... maybe we could keep the NLS settings with each number we store in our varchar field (oh man please no!).
So you get no sympathy from many here when you store numbers (and dates) in a varchar field. Yes you can do what you want, but there are problems that sooner or later you will be addressing. If you are lucky, when this happens you will be working somewhere else. If you are unlucky, that somewhere else will have had another develoer/designer team that didn't address the issue when it was their turn cause it was easier to leave it for you to deal with.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
