(PL)SQL - Regular Expressions

I encountered some strange behaviour when trying to remove all characters from a varchar2, except a-z, A-Z and 0-9.
Using regular expressions, you can define that in a number of ways, such as [^A-Za-z0-9] or [^[:alnum:]]

Example 1 works as expected (see the query below):
For A1 the ë (ascii 235) is not included, since it falls outside of the [^A-Za-z0-9] range.
For A2 the ë is included, since it is first converted to a regular e character.

Example 2 is (a dummy version of) the exact same query on another (also 11gR2) database and client. Here, the result for A1 was a bit different:
For A1 the ë was included.
A2 was the same.

Probably the characterset settings of the databases and/or clients were the cause? I have included the info gathered by PL/SQL Developer for both setups.
My favourite online regular expressions tool regex101.com agrees that example 1 is as expected.

A1	A2
Ab3X1	Ab3eX1

Character size: 4 byte(s)

CharSetID: 873

NCharSetID: 2000

Unicode Support: True

NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252

NLS_CHARACTERSET: AL32UTF8

NLS_NCHAR_CHARACTERSET: AL16UTF16

A1	A2
Ab3ëX1	Ab3eX1

Character size: 1 byte(s)

CharSetID: 31

NCharSetID: 2000

Unicode Support: True

NLS_LANG: DUTCH_THE NETHERLANDS.WE8MSWIN1252

NLS_NCHAR_CHARACTERSET: AL16UTF16

NLS_CHARACTERSET: WE8ISO8859P1

A simple example how to split string like 'ABC:123;XYZ:789' into multiple value pairs.

R	Value 1	Value 2
ABC:123	ABC	123
ABCD:1234	ABCD	1234
WXYZ:6789	WXYZ	6789
XYZ:789	XYZ	789

Creating a simple check for phone numbers was not as easy as I expected.
Getting it to work in regex101.com was easy, but that expression was not handled as expected when using it in the Oracle database.
I did get it to work by changing the expression, but I'm still not sure why the first version didn't work.

Tel	R_1	R_2
088 223 22 33	0	1
+31 (0)88 - 223 22 33	0	1
088-223 22 33	0	1
0882232233	0	1
abc 123	0	0

1 - 5

Maybe even worse than phone numbers are email addresses.

The email address examples listed in https://en.wikipedia.org/wiki/Email_address were an eye-opener for me - I had no idea there were so many types of valid email addresses.
Well, in theory anyway, because a lot of the more exotic ones will not be accepted in a lot of places.
To check which regular expression to use for email validation, I applied quite a lot of them to the list from Wikipedia. See the report below - 0 means the validation failed, 1 means it passed.
Most of these regular expressions come from various websites I found, but I did come up with some of them myself.
As you may expect, there is no "best solution" really. None of the expressions I found give a 100% accurate score.

So for everyday use, I would recommend option x15 - or maybe x14 if you also want to include "local" email addresses like user@server01.
Those should cover over 99% of the valid email addresses you will encounter in the real world.

S	E	X Org	X1	X3	X4	X6	X7	X10	X11	X12	X13	X14	X15
1	simple@example.com	1	1	1	1	1	1	1	1	1	1	1	1
2	very.common@example.com	1	1	1	1	1	1	1	1	1	1	1	1
3	FirstName.LastName@EasierReading.org	1	1	1	0	1	1	1	1	1	1	1	1
4	x@example.com	1	1	1	1	1	1	1	1	1	1	1	1
5	long.email-address-with-hyphens@and.subdomains.example.com	1	1	1	1	1	1	1	1	1	1	1	1
6	user.name+tag+sorting@example.com	1	1	0	1	1	0	1	1	1	1	1	1
7	name/surname@example.com	1	0	0	0	0	0	0	1	1	1	1	1
8	admin@example	1	0	0	0	0	0	0	0	0	1	1	0
9	example@s.example	1	0	0	1	1	0	1	1	1	1	1	1
10	" "@example.org	1	0	0	0	0	0	0	0	1	1	1	1
11	"john..doe"@example.org	1	0	0	0	0	0	0	1	1	1	1	1
12	mailhost!username@example.org	1	0	0	0	0	0	0	1	1	1	1	1
13	"very.(),:;<>[]\".VERY.\"very@\\ \"very\".unusual"@strange.example.com	1	0	0	0	0	0	0	0	1	1	0	0
14	user%example.com@example.org	1	1	0	1	0	0	1	1	1	1	1	1
15	user-@example.org	1	1	1	1	1	1	1	1	1	1	1	1
16	postmaster@[123.123.123.123]	1	0	0	0	0	1	0	1	1	1	1	1
17	postmaster@[IPv6:2001:0db8:85a3:0000:0000:8a2e:0370:7334]	1	0	0	0	0	0	0	0	0	1	1	0
18	_test@[IPv6:2001:0db8:85a3:0000:0000:8a2e:0370:7334]	1	0	0	0	0	0	0	0	0	1	1	0
19	I❤️CHOCOLATE@example.com	1	0	0	0	0	0	0	1	1	1	1	1
20	abc.example.com	0	0	0	0	0	0	0	0	0	0	0	0
21	a@b@c@example.com	0	0	0	0	0	0	0	1	1	1	0	0
22	a"b(c)d,e:f;g<h>i[j\k]l@example.com	0	0	0	0	0	0	0	1	1	1	1	1
23	just"not"right@example.com	0	0	0	0	0	0	0	1	1	1	1	1
24	this is"not\allowed@example.com	0	0	0	0	0	0	0	0	1	1	1	1
25	this\ still\"not\\allowed@example.com	0	0	0	0	0	0	0	0	1	1	1	1
26	1234567890123456789012345678901234567890123456789012345678901234+x@example.com	0	1	0	1	1	0	1	1	1	1	1	1
27	i.like.underscores@but_they_are_not_allowed_in_this_part	0	0	0	0	0	0	0	0	0	1	1	0

Region

Identification
	Sequence	10
	Title	Accented Characters Example 1
	Type	Report
Source
	Source Type	Report
	Region Source	select regexp_replace( '$Ab3@ëX 1' , '[^A-Za-z0-9]' ) as a1 , regexp_replace( convert('$Ab3@ëX 1', 'US7ASCII'), '[^A-Za-z0-9]' ) as a2 from dual

Region

Identification
	Sequence	30
	Title	Accented Characters Example 2
	Type	Report
Source
	Source Type	Report
	Region Source	select 'Ab3ëX1' as a1 , 'Ab3eX1' as a2 from dual

Region

Identification
	Sequence	10
	Title	Value Pairs Example
	Type	Report
Source
	Source Type	Report
	Region Source	with t as ( select 'ABC:123;XYZ:789' as col from dual union all select 'ABCD:1234;WXYZ:6789' as col from dual ) , t2 as ( select t0.column_value as r from t cross join table(apex_string.split(t.col, ';')) t0 ) select t2.r , trim( ':' from regexp_substr( t2.r, '^.:' ) ) as value_1 , trim( ':' from regexp_substr( t2.r, ':.$' ) ) as value_2 from t2 order by 1 /* Note that apex_util.string_to_table was only introduced with Apex 5.1 (and apex_string.split in a later version), so for older versions, or a schema without Apex access, you can use xmltable (simply replace the t2 query above with the query below): select extract(column_value, '//text()').getStringVal() as r from t , xmltable( replace( '''' \|\| t.col \|\| '''', ';', ''', ''') ) */

Region

Identification
	Sequence	10
	Title	Phone Numbers Example
	Type	Report
Source
	Source Type	Report
	Region Source	with a as ( select '088 223 22 33' as tel from dual union all select '+31 (0)88 - 223 22 33 ' as tel from dual union all select '088-223 22 33' as tel from dual union all select '0882232233' as tel from dual union all select 'abc 123' as tel from dual ) select a.tel , regexp_instr(a.tel, '^[\d\| \|\-\|+\|(\|)]+$' ) as r_1 -- works in regex101.com, not in Oracle database , regexp_instr(a.tel, '^(\d\| \|-\|\+\|$\|$)+$') as r_2 -- works in both from a

Region

Identification
	Sequence	20
	Title	Email Example
	Type	Report
Source
	Source Type	Report
	Region Source	with a as ( -- https://en.wikipedia.org/wiki/Email_address -- Valid email addresses select 01 as s, 'simple@example.com' as e, 1 as x_org from dual union all select 02 as s, 'very.common@example.com' as e, 1 as x_org from dual union all select 03 as s, 'FirstName.LastName@EasierReading.org' as e, 1 as x_org from dual union all -- (case is always ignored after the @ and usually before) select 04 as s, 'x@example.com' as e, 1 as x_org from dual union all -- (one-letter local-part) select 05 as s, 'long.email-address-with-hyphens@and.subdomains.example.com' as e, 1 as x_org from dual union all select 06 as s, 'user.name+tag+sorting@example.com' as e, 1 as x_org from dual union all -- (may be routed to user.name@example.com inbox depending on mail server) select 07 as s, 'name/surname@example.com' as e, 1 as x_org from dual union all -- (slashes are a printable character, and allowed) select 08 as s, 'admin@example' as e, 1 as x_org from dual union all -- (local domain name with no TLD, although ICANN highly discourages dotless email addresses[32]) select 09 as s, 'example@s.example' as e, 1 as x_org from dual union all -- (see the List of Internet top-level domains) select 10 as s, '" "@example.org' as e, 1 as x_org from dual union all -- (space between the quotes) select 11 as s, '"john..doe"@example.org' as e, 1 as x_org from dual union all -- (quoted double dot) select 12 as s, 'mailhost!username@example.org' as e, 1 as x_org from dual union all -- (bangified host route used for uucp mailers) select 13 as s, '"very.(),:;<>[]\".VERY.\"very@\\ \"very\".unusual"@strange.example.com' as e, 1 as x_org from dual union all -- (include non-letters character AND multiple at sign, the first one being double quoted) select 14 as s, 'user%example.com@example.org' as e, 1 as x_org from dual union all -- (% escaped mail route to user@example.com via example.org) select 15 as s, 'user-@example.org' as e, 1 as x_org from dual union all -- (local-part ending with non-alphanumeric character from the list of allowed printable characters) select 16 as s, 'postmaster@[123.123.123.123]' as e, 1 as x_org from dual union all -- (IP addresses are allowed instead of domains when in square brackets, but strongly discouraged) select 17 as s, 'postmaster@[IPv6:2001:0db8:85a3:0000:0000:8a2e:0370:7334]' as e, 1 as x_org from dual union all -- (IPv6 uses a different syntax) select 18 as s, '_test@[IPv6:2001:0db8:85a3:0000:0000:8a2e:0370:7334]' as e, 1 as x_org from dual union all -- (begin with underscore different syntax) -- Valid email addresses with SMTPUTF8 select 19 as s, 'I❤️CHOCOLATE@example.com' as e, 1 as x_org from dual union all -- (emoji are only allowed with SMTPUTF8) -- Invalid email addresses select 20 as s, 'abc.example.com' as e, 0 as x_org from dual union all -- (no @ character) select 21 as s, 'a@b@c@example.com' as e, 0 as x_org from dual union all -- (only one @ is allowed outside quotation marks) select 22 as s, 'a"b(c)d,e:f;g<h>i[j\k]l@example.com' as e, 0 as x_org from dual union all -- (none of the special characters in this local-part are allowed outside quotation marks) select 23 as s, 'just"not"right@example.com' as e, 0 as x_org from dual union all -- (quoted strings must be dot separated or be the only element making up the local-part) select 24 as s, 'this is"not\allowed@example.com' as e, 0 as x_org from dual union all -- (spaces, quotes, and backslashes may only exist when within quoted strings and preceded by a backslash) select 25 as s, 'this\ still\"not\\allowed@example.com' as e, 0 as x_org from dual union all -- (even if escaped (preceded by a backslash), spaces, quotes, and backslashes must still be contained by quotes) select 26 as s, '1234567890123456789012345678901234567890123456789012345678901234+x@example.com' as e, 0 as x_org from dual union all -- (local-part is longer than 64 characters) select 27 as s, 'i.like.underscores@but_they_are_not_allowed_in_this_part' as e, 0 as x_org from dual -- (underscore is not allowed in domain part) ) select a.* , regexp_instr(a.e, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') as x1 -- https://forums.oracle.com/ords/apexds/post/email-validation-using-regular-expressions-3709 , regexp_instr(a.e, '^[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}') as x2 , regexp_instr(a.e, '^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,6}$') as x3 , regexp_instr(a.e, '^[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}$') as x4 -- disabled, gives ORA-12728: invalid range in regular expression: regexp_instr(a.e, '(?:[a-z0-9!#$%&''+/=?^_`{\|}~-]+(?:\.[a-z0-9!#$%&''+/=?^_`{\|}~-]+)\|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]\|\\[\x01-\x09\x0b\x0c\x0e-\x7f])")@(?:(?:[a-z0-9](?:[a-z0-9-][a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-][a-z0-9])?\|\[(?:(?:25[0-5]\|2[0-4][0-9]\|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]\|2[0-4][0-9]\|[01]?[0-9][0-9]?\|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]\|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])') as x5 , regexp_instr(a.e, '^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$') as x6 , regexp_instr(a.e, '^([a-zA-Z0-9_\.-]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)\|(([a-zA-Z0-9-]+\.)+))([a-zA-Z]{2,4}\|[0-9]{1,3})(\]?)$') as x7 -- https://forums.oracle.com/ords/apexds/post/apex-validating-an-email-address-using-regex-9730 -- , regexp_instr(a.e, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,63}$') as x10 -- x1 + TLD 63 , regexp_instr(a.e, '^\S+@\S+\.\S+$') as x11 , (case when a.e like '%@%.%' then 1 else 0 end ) as x12 , (case when a.e like '%@%' then 1 else 0 end ) as x13 , regexp_instr(a.e, '^[^@]+@[^@]+$') as x14 -- best so far (without .com): "any character(s) except @" + "@" + "any character(s) except @" , regexp_instr(a.e, '^[^@]+@[^@]+\.[^@]+$') as x15 -- best so far (with .com): "any character(s) except @" + "@" + "any character(s) except @" from a order by a.s

Accented Characters

Accented Characters Example 1

Accented Characters Example 2

Value Pairs

Value Pairs Example

Phone Numbers

Phone Numbers Example

Email

Email Example

Code

Region

Region

Region

Region

Region