How to make LIKE behave case-insensitive?

Question

I have an importedParameter which I want to search inside of a column in a SELECT.

But for now it is case-sensitive, how can I make it case-insensitive ?

I've tried multiple things: AND LOWER(columnName) LIKE LOWER(@lv_string) or AND columnName LIKE @tst_string COLLATE utf8_general_ci and some other stuff but got this error:

A Boolean expression is required in positions starting with LOWER(Q.

Sample code:

DATA(tst_string) = '%' && importedParamter && '%'.

IF anotherParameter IS NOT INITIAL.
  IF importedParamter IS NOT INITIAL.
    SELECT * FROM <table1> as p
      INNER JOIN <table2> as q on q~column1 = p~column1
      WHERE p~column2 = @anotherParameter
      AND q~column2 LIKE @tst_string
      INTO CORRESPONDING FIELDS OF TABLE @anotherName
  ENDIF.
ENDIF.

Answer 1

This code works fine for me:

SELECT * 
  FROM adrp
  WHERE LOWER( name_first ) LIKE 'phi%'
  INTO TABLE @DATA(results).

It finds my personal data entry (as well as those of another "Philipp" and of a "Philip"), even though we are all spelled with a capital P.

LIKE LOWER( 'Phi%' ) does not work, but when you can't control the input, then you can convert it to lower case before the select:

DATA(tst_string) = 'Phi%'.
TRANSLATE tst_string TO LOWER CASE.
SELECT *
  FROM adrp
  WHERE LOWER( name_first ) LIKE @tst_string
  INTO TABLE @DATA(results).

Release: 7.54

I am not sure which release specifically allowed functions like LOWER within the WHERE clause. According to the comments, it should work since 7.51.