Search doublicates until special string

[sc4ry]

Dear all,

I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away.

Current formula is:

Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC;

testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why.

Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC;

Error: near "(": syntax error:

Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/

Thanks a lot for your help.

EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr

(substr(a.title, 1, instr(a.title, ' - '))

 

Edited August 27, 2017 by sc4ry