Oracle To_Char函数，如果它已经是一个字符串，该如何处理

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'.

场景:我正在调用一个函数，该函数返回用户输入的字段。该字段通常返回“120000”之类的数字，然后使用to_char将其转换为“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.

问题:有些用户输入“120,000”之类的值，在尝试使用to_char时会出现错误。如果没有找到值，函数也会返回一个空格。我之前用to_number做过一些尝试，但它和“我相信”有问题。

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

问:处理这个问题最好的方法是什么?Case statement check for the '，'?使用to_number to_char ?

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

注意:我可以一起破解一个解决方案，我只是想知道最好的办法是什么。

1 个解决方案

#1

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

与其使用REPLACE，不如使用更强大的REGEXP_REPLACE函数。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.

这将用null替换所有非数字字符，有效地从字符串中删除它们。

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

请参见这个答案:Oracle:用字符串替换非数字字符。

#1