Hi All,

I have a data-table which contains field with lists of crops:

Mangoes, Apples, Cashew Nuts
Cashew Nuts, Turmeric, Mangoes, Black Pepper
Mangoes, Apples, Cashew Nuts
...

The lists are all well formed (they are actually created by selecting items from a List-Box in a Delphi App.) all instances of "Mangoes" is the same, every item is followed by a comma.

If I write:

SELECT
 LIST(DISTINCT MyField)
FROM MyTable

From the above example data Rows 1 and 3 would only be selected once, but Mangoes and Cashew Nuts would appear twice.

The result would be:

Mangoes, Apples, Cashew Nuts,Cashew Nuts, Turmeric, Mangoes, Black Pepper

I want to then remove repeated items in this list, so it ends up:

Mangoes, Apples, Cashew Nuts,Turmeric, Black Pepper

Is there any way to do this with EDB SQL?

Thanks in advance.

Adam


Quick answer - nope, at least without delving into SQL/PSM or user defined functions

The problem is that ElevateDB is working on a structured string and it doesn't understand structure. My solution would be either to do it in Delphi or write a simple Delphi UDF eg something like:


function Uniquify(const FldStr: string): string;
var
sl: TStringList;
begin
if Length(FldStr) = 0 then Exit;
sl := TStringList.Create;
sl.Duplicates := dupIgnore;
sl.Ordered := True;
sl.CommaText := FldStr;
Result := sl.CommaText;
sl.Free;
end;

First, to make it easier change

LIST(DISTINCT MyField)

to

LIST(DISTINCT MyField, ',')


Roy Lambert

Dear Roy,

Good and clear (and what I probably thought!)

I will quite likely write an EDB DLL, or try to rethink the problem so I can handle it on the Delphi end. As you point out, if I pass the "with duplicates" list up to Delphi, it can flush out the duplicates effortlessly. I was hoping to make the functionality available at DB Level.

>>First, to make it easier change
>>LIST(DISTINCT MyField)
>>to
>>LIST(DISTINCT MyField, ',')

Useful addition! Thanks.

Adam


What are you wanting to do when you have the list?

Roy Lambert