Unexpected Results from regexp_replace
От | Susan Hurst |
---|---|
Тема | Unexpected Results from regexp_replace |
Дата | |
Msg-id | 3bc24f543d53abb1ac4f010bb9ac45f3@mail.brookhurstdata.net обсуждение исходный текст |
Ответы |
Re: Unexpected Results from regexp_replace
|
Список | pgsql-general |
I would like to extract only letters and numbers [0-9a-z] from an input string. Using regexp_replace in postgres, I get unexpected results. The pattern matching seems to stop before the end of the string value is reached. Some of the values that should have been excluded are still there. What is the trick for removing all instances of unwanted characters and spaces? I tried regexp_matches but the results were no better. In Oracle, the same code produces the expected results. Below is the code and the output from postgres and from oracle, for your reference. Thanks for your help! Sue ----------------------------------------------------------------------- POSTGRESQL: Incorrect results...left over unwanted characters create table test_str (greeting varchar(256),v_greeting varchar(256)); select * from test_str; insert into test_str (greeting) values ('Hello Worldy'); insert into test_str (greeting) values ('Hello, World!'); insert into test_str (greeting) values ('hello_world'); insert into test_str (greeting) values ('Hello! World.'); insert into test_str (greeting) values ('hello - world'); insert into test_str (greeting) values ('hello_world n'); insert into test_str (greeting) values ('hello world n n'); select greeting,regexp_replace (lower(greeting),'[^0-9a-z]{1,}','') str from test_str ; greeting str Hello Worldy helloworldy Hello, World! helloworld! hello_world helloworld Hello! World. helloworld. hello - world helloworld hello_world n helloworld n hello world n n helloworld n n ------------------------------------------------------------------------ ORACLE: Correct results...all unwanted characters are gone create table test_str (greeting varchar2(256),v_greeting varchar2(256)); select * from test_str; insert into test_str (greeting) values ('Hello Worldy'); insert into test_str (greeting) values ('Hello, World!'); insert into test_str (greeting) values ('hello_world'); insert into test_str (greeting) values ('Hello! World.'); insert into test_str (greeting) values ('hello - world'); insert into test_str (greeting) values ('hello_world n'); insert into test_str (greeting) values ('hello world n n'); select greeting,regexp_replace (lower(greeting),'[^0-9a-z]{1,}','') str from test_str ; GREETING STR Hello Worldy helloworldy Hello, World! helloworld hello_world helloworld Hello! World. helloworld hello - world helloworld hello_world n helloworldn hello world n n helloworldnn -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261
В списке pgsql-general по дате отправления: