Delete duplicate alphanumeric entries from column data in SQL

You can use regular expressions to remove duplicate consecutive alphanumeric characters from column data in SQL. The REPLACE_REGEXPR function with pattern matching allows you to identify and replace repeated characters with a single occurrence.

Syntax

The basic syntax for removing duplicate alphanumeric entries using regular expressions is ?

REPLACE_REGEXPR ('([A-Za-z0-9])\1+' in column_name
   WITH '\1'
   OCCURRENCE ALL)

Where ([A-Za-z0-9])\1+ is the regex pattern that captures any alphanumeric character and matches one or more consecutive occurrences of the same character.

Example

Here's a practical example showing how to remove duplicate consecutive characters from a string ?

SELECT REPLACE_REGEXPR ('([A-Za-z0-9])\1+' in 'BB11222343CC'
   WITH '\1'
   OCCURRENCE ALL)
FROM dual;

Output

B12343C

How It Works

The regular expression pattern breaks down as follows:

• ([A-Za-z0-9]) ? Captures any single alphanumeric character
• \1+ ? Matches one or more consecutive occurrences of the captured character
• WITH '\1' ? Replaces the matched pattern with just the single captured character
• OCCURRENCE ALL ? Applies the replacement to all occurrences in the string

Conclusion

Using REPLACE_REGEXPR with regular expressions provides an efficient way to remove duplicate consecutive alphanumeric characters from SQL column data, helping clean and standardize your data.