PL/SQL从电话号码中删除不需要的字符

I have looked around and cannot seem to find a straight answer to this question. I am confused as to how I would do this. Should I be using a regular expression or is there a better way to do this.

我环顾四周，似乎找不到这个问题的正确答案。我不知道该怎么做。我应该使用正则表达式，还是有更好的方法。

I have a procedure that formats phone numbers depending on what they start with and how long they are. I want to be able to clean the phone number first, before formatting it. So my tutor has thrown in a phone number that looks like this

我有一个程序，根据电话号码的起始位置和时长来设置电话号码的格式。我想在格式化之前先清理一下电话号码。我的导师输入了一个像这样的电话号码

07A3345&6543

07年a3345&6543

I want to remove the A and the & from the phone number before formatting it.

在格式化之前，我想删除A和&从电话号码。

Thank you

谢谢你！

2 个解决方案

#1

You can do this using translate(). One way is by being explicit about what you want to remove:

您可以使用translate()来完成这个任务。一种方法是明确你想删除的内容:

select translate(phone, '0123456789', '0123456789A&')

Another way is to be more clever and just keep the numbers:

另一种方法是更聪明，只保留数字:

select translate(phone, '0123456789' || translate(phone, 'a0123456789', 'a'), '0123456789')

This will drop all the non-numeric characters.

这将删除所有非数字字符。

#2

If you just need to remove non-digit characters, you might try this:

如果您只需要删除非数字字符，您可以尝试以下操作:

TRANSLATE(phone,'0123456789'||phone,'0123456789')

This will map characters 0 through 9 to themselves, and any other character present in the phone number to nothing -- actually discarding them.

这将把0到9的字符映射到它们自己，将电话号码中出现的任何其他字符映射为none——实际上是丢弃它们。

Given your example:

鉴于你的例子:

DECLARE
  phone VARCHAR(20) := '07A3345&6543';
BEGIN
  DBMS_OUTPUT.PUT_LINE(TRANSLATE(phone,'0123456789'||phone,'0123456789'));
END

Will display:

将显示:

0733456543

#1