oracle安装时空格字符,Oracle PL/SQL:從字符串中刪除“空格字符”

In my Oracle 10g database I would like to remove "space characters" (spaces, tabs, carriage returns...) from the values of a table field.

在Oracle 10g數據庫中，我希望從表字段的值中刪除“空間字符”(空格、制表符、回車符…)。

Is TRANSLATE() the way to go ? For example something like:

翻譯是正確的方法嗎?例如像:

MY_VALUE := TRANSLATE(MY_VALUE,

CHR(9) || CHR(10) || CHR(11) || CHR(12) || CHR(13) || ' ', '');

Or is there any better alternative (something like [:space:] in PHP PCRE) ?

或者有沒有更好的選擇(比如:在PHP PCRE中)?

Thanks for any piece of advice.

謝謝你的任何建議。

6 个解决方案

#1

36

I'd go for regexp_replace, although I'm not 100% sure this is usable in PL/SQL

我選擇regexp_replace，盡管我不能100%確定它在PL/SQL中是否可用

my_value := regexp_replace(my_value, '[[:space:]]*','');

#2

11

Shorter version of:

短版的:

REGEXP_REPLACE( my_value, '[[:space:]]', '' )

Would be:

是:

REGEXP_REPLACE( my_value, '\s')

Neither of the above statements will remove "null" characters.

上述任何一條語句都不會刪除“null”字符。

To remove "nulls" encase the statement with a replace

要刪除“nulls”，請使用替換將語句括起來

Like so:

像這樣:

REPLACE(REGEXP_REPLACE( my_value, '\s'), CHR(0))

#3

7

Since you're comfortable with regular expressions, you probably want to use the REGEXP_REPLACE function. If you want to eliminate anything that matches the [:space:] POSIX class

由於您熟悉正則表達式，您可能希望使用REGEXP_REPLACE函數。如果您想消除任何與[:space:] POSIX類匹配的內容

REGEXP_REPLACE( my_value, '[[:space:]]', '' )

SQL> ed

Wrote file afiedt.buf

1 select '|' ||

2 regexp_replace( 'foo ' || chr(9), '[[:space:]]', '' ) ||

3 '|'

4* from dual

SQL> /

'|'||

-----

|foo|

If you want to leave one space in place for every set of continuous space characters, just add the + to the regular expression and use a space as the replacement character.

如果要為每組連續空格字符保留一個空格，只需將+添加到正則表達式中，並使用空格作為替換字符。

with x as (

select 'abc 123 234 5' str

from dual

)

select regexp_replace( str, '[[:space:]]+', ' ' )

from x

#4

2

select regexp_replace('This is a test ' || chr(9) || ' foo ', '[[:space:]]', '') from dual;

REGEXP_REPLACE

--------------

Thisisatestfoo

#5

1

To remove any whitespaces you could use:

刪除任何你可以使用的空白:

myValue := replace(replace(replace(replace(replace(replace(myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13));

Example: remove all whitespaces in a table:

示例:刪除表中的所有白空間:

update myTable t

set t.myValue = replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13))

where

length(t.myValue) > length(replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13)));

or

或

update myTable t

set t.myValue = replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13))

where

t.myValue like '% %'

#6

0

To replace one or more white space characters by a single blank you should use {2,} instead of *, otherwise you would insert a blank between all non-blank characters.

要將一個或多個空格字符替換為一個空格，您應該使用{2，}而不是*，否則將在所有非空格字符之間插入空格。

REGEXP_REPLACE( my_value, '[[:space:]]{2,}', ' ' )