how to allow accented charaters in oracle using regexp_replace in oracle?

Question

Case: To accept person_name satisfying following criteria:

Allows any alphabetic symbols
Space
Dash
Apostrophe
Accent grave

Some pre-calculation has been performed to store the name in the string "PERSON_NAME"

LOGIC: SUBSTR(REGEXP_REPLACE(PERSON_NAME,'[^A-Za-z .`''-]+',''),0,50)

SELECT SUBSTR(REGEXP_REPLACE('cafè','[^A-Z|a-z| |.|`|''|-]+'),0,50) 
    FROM dual;

Passing almost all cases except in case of accented characters:

For example:

Expected result: cafè [i.e symbol above e ` should not be filtered out]

Actual Result: caf

what should be filtered out ? I mean what's the difference with the original string ? — Barbaros Özhan
– Barbaros Özhan, Commented Apr 11, 2019 at 13:54

Barbaros Özhan · Accepted Answer · 2019-04-12 06:19:17Z

1

You can use :

select SUBSTR(REGEXP_REPLACE('cafè-` t *'' {','[[:digit:]]') ,0,50) as "String"
  from dual;

String
--------------
cafè-` t *' {

since there's no information about numeric expressions in your restriction list.

edited Apr 12, 2019 at 6:19

answered Apr 11, 2019 at 13:40

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Patrick Bacon Over a year ago

Hi, your column aliasing as "String" results in an error being thrown: SQL Error: ORA-00907: missing right parenthesis

gooner_psy Over a year ago

Thank you, @BarbarosÖzhan. One last doubt: Is it a bad practice to put the accented vowels as a part of the REGEX itself? select substr(REGEXP_REPLACE('cafè','[^A-Z|a-z| |.||''|-|À|È|Ì|Ò|Ù|à|è|ì|ò|ù]+'),0,50) from dual; I want to allow to allow only the accent grave[] vowels. eg: allowed -vis-à-vis [result = visàvis] not allowed: Cissé [result = Ciss]

Barbaros Özhan Over a year ago

@Saiprasadshankar you're welcome. I think there's no need to list explicitly all the accented chars, if you use [[:digit:]], but I didn't understand from your last sample that you want to remove or not to remove non-alphabetic chars ...

gooner_psy Over a year ago

Hello @BarbarosÖzhan, Another extension of this task is to allow a minimum of 2 charachters and a maximum of 50. In my substr() I have taken 0 and 50. So I I start from the beginning of the string and get only a maximum of 50 charachtcers. Is there a way to specify the minimum range as well? As using SUBSTR(2,50) skips the first few charachters as that specifies the starting point.

Barbaros Özhan Over a year ago

@Saiprasadshankar can you explain what you mean with an example?

Collectives™ on Stack Overflow

how to allow accented charaters in oracle using regexp_replace in oracle?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related