Home » SQL & PL/SQL » SQL & PL/SQL » Seperate-Comma (merged)
Seperate-Comma (merged) [message #291210] Thu, 03 January 2008 06:56 Go to next message
swamitvk
Messages: 14
Registered: November 2007
Junior Member

Hi

in oracle Is there any function to remove comma from numbers.

Can anybody help me?


Thank You
Re: Seperate-Comma [message #291213 is a reply to message #291210] Thu, 03 January 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the meaning of this sentence?
A number does not have comma. Only its representation in string may have.
Use TO_CHAR.

Regards
Michel
Re: Seperate-Comma [message #291217 is a reply to message #291210] Thu, 03 January 2008 07:24 Go to previous messageGo to next message
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
Re: Seperate-Comma [message #291220 is a reply to message #291210] Thu, 03 January 2008 07:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Or check for REPLACE

Thumbs Up
Rajuvan.
sql for migration [message #291222 is a reply to message #291210] Thu, 03 January 2008 07:39 Go to previous messageGo to next message
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: sql for migration [message #291225 is a reply to message #291222] Thu, 03 January 2008 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See other topic on the same subject (and same IP, you should consider talking each other).

And now merged.

Regards
Michel

[Updated on: Thu, 03 January 2008 07:50]

Report message to a moderator

Re: Seperate-Comma (merged) [message #291544 is a reply to message #291210] Fri, 04 January 2008 15:22 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: trigger
Next Topic: ref cursors / without dynamic sql
Goto Forum:
  


Current Time: Wed Dec 07 05:18:25 CST 2016

Total time taken to generate the page: 0.22644 seconds