Select similar columns

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 2.5.9 Win32.

There is a request that I should display similar earlier records when adding a new product. Because several different people do inputs, there is a possibility that the same product with slightly different code already exists.

For example;

Existing product code: 123-456-ABC

User might input: 123456ABC or 123 456-ABC or 123 456 ABC or 123456

Dash or space are just examples. There is no rigid product code mask. It is freehand input.

What is requested is to display a warning with a list of similar existing products. I have no idea if that is possible or not.

Any help would be appreciated.

Thanks & Regards,

Ertan Küçükoğlu

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 25.11.2021 12:38:
> What is requested is to display a warning with a list of similar existing
> products. I have no idea if that is possible or not.

It cannot be done in Firebird out-of-box. But you can select full list of
existing product records to client and use Levenshtein distance (or other edit
distance metrics) to find similarities.

--
WBR, SD.

[Karol Bieniaszewski]

There are few possibilities:

1. Normalize product code in imput tools.
2. Add product code generator e.g. in before insert trigger.
3. Avoid duplication by creating unique expression index e.g. CREATE UNIQUE INDEX IXAU_TABLE_NAME__CODE ON TABLE_NAME COMPUTED BY(REPLACE(REPLACE(CODE, ' ', ''), '-', ''))
   You can add more chars… But i do not remember if this is available in FB2.5 series.

 

regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4ydffUGY5NLATqzo%2BADX1wkM6NY5%3D%3DhDsy5rMEvy6HENSA%40mail.gmail.com.

 

[Tim Crawford]

Is FirebirdSQL some sort of  API or query tool?

And it doesn't support the WHERE column LIKE or SIMILAR ?