Case insensitive where clause for Like statement

[Todd Flora]

We need a way to do a case insensitive search on a Like where type.

We have done the following to accomplish this. Please consider adding this to the Code Base

Spring.Persistence.Sql.Types

Add a new TMatchMode type for Ignore Case for mmAnywhere

                                                    [ ------- Added ----]
  TMatchMode = (mmExact, mmStart, mmEnd, mmAnywhere, mmAnywhereIgnoreCase);

Modify TSQLWhereField to have a property for IgnoringCase.

 TSQLWhereField = class(TSQLParamField)
  private
    fWhereOperator: TWhereOperator;
    fLeftSQL: string;
    fRightSQL: string;
    fParamName2: string;
    FIgnoreCase: Boolean;               <--Added -------
  public
    constructor Create(const name: string; const table: TSQLTable); reintroduce; overload;
    constructor Create(const leftSQL, rightSQL: string); reintroduce; overload;
    property WhereOperator: TWhereOperator read fWhereOperator write fWhereOperator;
    property LeftSQL: string read fLeftSQL write fLeftSQL;
    property RightSQL: string read fRightSQL write fRightSQL;
    property ParamName2: string read fParamName2 write fParamName2;
    property IgnoreCase : Boolean read FIgnoreCase write FIgnoreCase; <--Added -------
  end;

constructor TSQLWhereField.Create(const leftSQL, rightSQL: string);
begin
  inherited Create(name, table, nil, ':' + name);
  fIgnoreCase := False;             <--Added -------
  fWhereOperator := woOr;
  fLeftSQL := leftSQL;
  fRightSQL := rightSQL;
end;

Modify GetMatchModeString to return Upper Case Pattern

function GetMatchModeString(matchMode: TMatchMode; const pattern: string): string;
const
  MATCH_CHAR = '%';
begin
  case matchMode of
    mmExact: Result := pattern;
    mmStart: Result := pattern + MATCH_CHAR;
    mmEnd: Result := MATCH_CHAR + pattern;
    mmAnywhere: Result := MATCH_CHAR + pattern + MATCH_CHAR;
    mmAnywhereIgnoreCase : Result := MATCH_CHAR + pattern.ToUpper + MATCH_CHAR; <--Added -------
  end;
  Result := QuotedStr(Result);
end;

Spring.Persistence.Criteria.Criterion.LikeExpression

Set Ignore Case to true on WhereField when TMatchMode is the new Mode.

function TLikeExpression.ToSqlString(const params: IList<TDBParam>;
  const command: TWhereCommand; const generator: ISQLGenerator;
  addToCommand: Boolean): string;
var
  whereField: TSQLWhereField;
begin
  whereField := TSQLWhereField.Create(PropertyName, GetCriterionTable(command));
  whereField.WhereOperator := WhereOperator;
  whereField.RightSQL := GetMatchModeString(fMatchMode, Value.AsString);
  whereField.IgnoreCase := (FMatchMode = mmAnywhereIgnoreCase);   <--Added -------
  Result := generator.GenerateWhere(whereField);

  if addToCommand then
    command.WhereFields.Add(whereField)
  else
    whereField.Free;
end;

Spring.Persistence.SQL.Generators.Ansi

Check for IgnoreCase and use SQL92 Upper Statement

function TAnsiSQLGenerator.GenerateWhere(const field: TSQLWhereField): string;
var
  fieldName : String;                  <--Added -------
begin
  if field is TSQLWherePropertyField then
    Result := Format('(%s %s %s)', [
      field.Table.Alias + '.' + field.LeftSQL,
      WhereOperatorNames[field.WhereOperator],
      TSQLWherePropertyField(field).OtherTable.Alias + '.' + field.RightSQL])
  else
    case field.WhereOperator of
      woIsNull, woIsNotNull:
        Result := GetQualifiedFieldName(field) + ' ' + WhereOperatorNames[field.WhereOperator];
      woLike, woNotLike, woIn, woNotIn:
      begin
        // TODO: support parameter
        fieldName := GetQualifiedFieldName(field); <--Added -------
        if field.IgnoreCase then                                <--Added -------
           fieldName := 'UPPER(' + fieldName + ')';   <--Added -------
        Result := Format('%s %s %s', [
            fieldName,                                                <--Changed -------
            WhereOperatorNames[field.WhereOperator],
            field.RightSQL])
      end;
      woOr, woAnd:
        Result := Format('(%s %s %s)', [
          field.LeftSQL,
          WhereOperatorNames[field.WhereOperator],
          field.RightSQL]);
      woNot:
        Result := Format('%s (%s)', [
          WhereOperatorNames[field.WhereOperator],
          field.LeftSQL]);
      woOrEnd, woAndEnd, woNotEnd: Result := '';
      woJunction: Result := Format('(%s)', [field.LeftSQL]);
      woBetween:
        Result := Format('(%s %s %s AND %s)', [
          GetQualifiedFieldName(field),
          WhereOperatorNames[field.WhereOperator],
          field.ParamName,
          field.ParamName2]);
    else
      Result := Format('%s %s %s', [
        GetQualifiedFieldName(field),
        WhereOperatorNames[field.WhereOperator],
        field.ParamName]);
    end;
end;

[Stefan Glienke]

Thanks for reporting this and posting a suggested fix. But we will not implement it like that and here is why:

You are mixing the case sensitivity handling with the match mode which also only was implemented by you for match mode anywhere and not for the other three.

It also only implements this for the Like statement and not for other possible places that could support case insensitivity.

[Todd Flora]

Understood. I agree that other criteria could also use case insensitive capability. If you can suggest how you would implement it I will make it so and provide the code.

[Stefan Glienke]

support for case insensitive like and in criteria (fixes #140)

→ <<cset 5fc305daa748>>

[Stefan Glienke]

support for case insensitive like and in criteria (fixes #140)

→ <<cset 5fc305daa748>>