Oracle在字符串内部修剪空格 [英] Oracle trim whitespace on the inside of a string

问题描述

我在系统中将电话号码存储为VARCHAR2，以便用户选择时可以在电话号码前输入'+'字符.

I am storing phone numbers as VARCHAR2 in my system to allow for users to input '+' characters infront of their phone number if they so choose.

我的正则表达式完美地做到了这一点，但是当将数字存储在数据库中时，我想去除用户可能输入的所有空格.

My regexp allows for this perfectly, but when storing the number in the database I want to strip out all whitespace the user may enter.

我的正则表达式允许以下格式

My regexp allows for the following formats

[phone]
[phone]
[phone]
01234567890
01234 567890
01234 567 890

我知道我可以通过不让用户在数字中添加空格来解决问题，但是我个人经验表明，由于客户端的某些愚蠢格式而导致验证错误是多么令人沮丧.我曾尝试在INSERT/UPDATE触发器上使用TRIM函数，但我意识到仅限于前后空格，Oracle中是否可以使用其他任何函数来删除内部空格?还是我需要编写自己的函数来做到这一点?

I know I could resolve my issue by not letting users put any whitespace in their numbers, but I know from personal experience just how frustrating it is to have a validation error due to some silly formatting on the client side. I have tried using the TRIM function on my INSERT/UPDATE trigger but I have realised that is limited to only front and trailing whitespace, is there any other function in Oracle I could use that would remove internal whitespace? Or do I need to write my own function to do this?

任何指针将不胜感激.

推荐答案

您要尝试replace (telno, ' ', '').

这篇关于Oracle在字符串内部修剪空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！