Showing results for 
Show  only  | Search instead for 
Did you mean: 

Find objects with duplicate names with ERQL

Honored Contributor

Hi Mega community.

Can we use ERQL to identify objects which are duplicates? 

For example,  Applications which have the same name ?   Or Person System objects which have the same email address ?

6 Replies



I would say the best answer depends what your use case is, and so what kind of output is fit to purpose

Zero cost option is to do an excel export of a metaclass and the attributes you want to look at and then use MS excel features around duplicates (e.g. Conditional formatting > highlight cells values rules > duplicate values)

-> if you look for duplicates, you probably want to resolve them by renaming, you can modify in excel and import back (while in excel you may also want to use additional text manipulation functions or data analysis plug in ; there is probably a lot that can be done without breaking the format and keeping the option to reimport the data cleansing result)

note : merging duplicates which are actually the same object is best performed in Hopex directly or via script

ERQL itself will have limited capabilities in terms of counting / looping on various inputs, but surely this can be handy as part of a report or a script to perform more advanced things without having to reimplement everything in the script (most of the times you would look for typos or close names but not exact duplicates),

for instance :

Select Application where [Short Name] like "Appli 0001"

-> will find the objects with the same name / beginning by the same string

Select Application where [Short Name] nearmatch "Appli 0001"

-> will find the objects with close names (e.g. at distance 1 or 2, like "Appli 0002" or "Appli 0011")

using # or ! wildcards you can do more specific things :

Select Application where [Short Name] like "Appli #1"

Select Application where [Short Name] like "Appli !0!!"

Hah! That's absolutely correct. It's one of those times when a straightforward request to voice in natural language is a nightmare for us non-techy nerds* to express in a query language.

*Some would disagree with that assessment!

MEGA NA Princpial Consultant

@ajpbradley Come to think of it.  Even better than enhancing the ERQL.   This might be something for the new AI capability to help with.  😉

Yeah, I totally understand.

MEGA NA Princpial Consultant

Honored Contributor

@ajpbradley Thanks Alan.  I'd hope so. We have few different use cases where we'd like to identify duplicates.


Hi Ben,

When I've asked people about this before, my understanding is that there needs to be a VB macro because ERQL doesn't allow a "compare."

Hopefully, something has changed, and someone else will contradict me with better news.


MEGA NA Princpial Consultant