3

Scenario: I am calling a function that returns a field that the user enters in. The field usually returns a number like '120000' which I then use to_char to convert into '120,000'.

Problem: Some users enter in values such as '120,000' which gives me an error when trying to use to_char. Also the function will return a space ' ' if no value is found. I tried something with to_number earlier and it has a problem with the ' ' I believe.

Question: What would be the best way to handle this problem? Case statement checking for the ','? Using to_number then to_char?

Note: I can hack a solution together I'm just wondering what the best way to handle this is.

Gage
  • 7,365
  • 9
  • 47
  • 77
  • Strip the commas out and then use `to_char` as above. Either that or tell the user to only enter digits. –  Jan 30 '12 at 19:08
  • 2
    The best way would be to prevent the users from entering numbers with commas in the first place. If you can't control that, then Jack's on the money - just strip the commas out with `REPLACE(field, ',', '')` – N West Jan 30 '12 at 19:17
  • 2
    I would force user to enter a number, not a formatted number (120,000). Also, some places use . instead of , so strip out commas won't always work. – tbone Jan 30 '12 at 19:22
  • Unfortunately we don't have that control. This is coming from a program called Amanda (by CSDC). It's an info code that anything can be entered into. I will use REPLACE thanks. – Gage Jan 30 '12 at 19:27

1 Answers1

7

Rather than using REPLACE you should use the more powerful REGEXP_REPLACE function. http://www.orafaq.com/wiki/REGEXP_REPLACE

You can then remove any non-numeric character from the string before then formatting it however you like.

In your case it would be something like:

REGEXP_REPLACE(<your field>, '[^0-9]+', '');

This replaces all non-numeric characters with null effectively removing them from the string.

See this answer too: Oracle: Replacing non-numeric chars in a string

Community
  • 1
  • 1
Ollie
  • 17,058
  • 7
  • 48
  • 59
  • What happens if the "LINKS" in the answers are broken in the future? :) I quote yourself from [here](http://stackoverflow.com/questions/12060917/using-nested-tables-in-java/12061531#comment16109755_12061531) – Anjan Biswas Aug 21 '12 at 20:25
  • @Annjawn, as you have appeared to trawl my previous answers for those containing links I can only assume you have taken my comment from the answer you have linked to in a way it wasn't intended. It was not designed to cause you offence. FWIW, I believe this answer stands, even if the link ever breaks as it contains both information as well as a link. Do you not agree? – Ollie Aug 21 '12 at 20:43
  • I do. But the links can break. Anytime, without any reason. But that doesn't mean we would stop using them. – Anjan Biswas Aug 21 '12 at 20:48
  • Hence I'd advocate both links and information. It gives a more persistent and helpful answer. As you can see, I happily use links and hope others do too but bear in mind they can and do break over time. – Ollie Aug 21 '12 at 20:52