Delphi Tstringlist, get strings as a quoted, comma delimited string?

Question

I have a Tstringlist containing a list of keys used in a database table. I'd like a simple way to generate one string containing all the keys, with each separated by a comma and enclosed in single quotes. This is so that it can be used in a SQL 'IN' statement eg WHERE FieldX IN ('One','Two','Three').

I've tried using quotechar but it is ignored when reading the commatext. eg the following code

procedure junk;
var
  SL : Tstringlist;
  s : string;
begin
 SL := Tstringlist.Create;
 SL.Delimiter :=','; //comma delimiter
 SL.QuoteChar := ''''; //single quote around strings
 SL.Add('One');
 SL.Add('Two');
 SL.Add('Three');
 try
   s :=  SL.commatext;
   showmessage(s);
 finally
   SL.Free;
 end; //finally
end; //junk

shows the message One,Two,Three - without any quotes.

I know I can do it the long way round, as in

procedure junk;
var
  SL : Tstringlist;
  s : string;
  i : integer;
begin
 SL := Tstringlist.Create;
 SL.Delimiter :=','; //comma delimiter
 SL.Add('One');
 SL.Add('Two');
 SL.Add('Three');
 try
 s := '';
 for I := 0 to SL.Count - 1 do
    begin
    s := s +  ',' + '''' + SL[i] + '''';
    end;
 delete(s,1,1);
 showmessage(s);
 finally
   SL.Free;
 end;//finally
end;

but is there a simpler way using properties of the Tstringlist itself?

Answer 1

If you're using D2006 or later, you can use a CLASS HELPER:

USES Classes,StrUtils;

TYPE
  TStringListHelper = CLASS HELPER FOR TStrings
                        FUNCTION ToSQL : STRING;
                      END;

FUNCTION TStringListHelper.ToSQL : STRING;
  VAR
    S : STRING;

  FUNCTION QuotedStr(CONST S : STRING) : STRING;
    BEGIN
      Result:=''''+ReplaceStr(S,'''','''''')+''''
    END;

  BEGIN
    Result:='';
    FOR S IN Self DO BEGIN
      IF Result='' THEN Result:='(' ELSE Result:=Result+',';
      Result:=Result+QuotedStr(S)
    END;
    IF Result<>'' THEN Result:=Result+')'
  END;

This code:

SL:=TStringList.Create;
SL.Add('One');
SL.Add('Two');
SL.Add('Number Three');
SL.Add('It''s number 4');
WRITELN('SELECT * FROM TABLE WHERE FIELD IN '+SL.ToSQL);

will then output:

SELECT * FROM TABLE WHERE FIELD IN ('One','Two','Number Three','It''s number 4')

Answer 2

Use sl.DelimitedText instead of sl.CommaText to make it follow your settings. CommaText will temporarily change the Delimiter and QuoteChar to some hardcoded values.