How to determine if the input data contains duplicate alphanumeric characters
February 1st, 2008 By Frank Zhou
The following two SQL patterns can be used to determine whether the data contains duplicated alphanumerical characters.
These patterns can also be used to implement a check constraint on a table.
variable str varchar2(100)
exec :str := 'abcdefg'
------------------------------SQL Solution before 10G-------------------
SELECT
CASE length('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') -
length(translate('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', chr(0)||:str, chr(0)))
WHEN length(:str) THEN 1
ELSE 0 END AS Flag
FROM dual;
FLAG
----------
1
exec :str := 'abcdefga'
SQL> /
FLAG
----------
0
create table no_dup_chars
(str varchar2(20),
constraint check_con check
(CASE length('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') -
length(translate('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', chr(0)||str, chr(0)))
WHEN length(str) THEN 1
ELSE 0 END = 1
)
);
insert into no_dup_chars values ('abc');
1 row created.
insert into no_dup_chars values ('abca');
ERROR at line 1:
ORA-02290: check constraint (XXX.CHECK_CON) violated
---------------------------------------------10G SQL Solution--------------------
exec :str := 'abcdefg'
SELECT CASE
WHEN REGEXP_SUBSTR(:str, '([[:alnum:]]+)(.*)\1′, 1, 1, ‘i’) IS NULL
THEN 1
ELSE 0 END AS flag
FROM dual;
FLAG
———-
1
exec :str := ‘abcdefga’
SQL> /
FLAG
———-
0
create table no_dup_chars_10G
(str varchar2(20),
constraint check_con_10g check
(REGEXP_SUBSTR(str, ‘([[:alnum:]]+)(.*)\1′, 1, 1, ‘i’) IS NULL
)
);
SQL> insert into no_dup_chars_10G values (’abc’);
1 row created.
SQL> insert into no_dup_chars_10G values (’abca’);
insert into no_dup_chars_10G values (’abca’)
*
ERROR at line 1:
ORA-02290: check constraint (XXXX.CHECK_CON_10G) violated