I am writing a query using LIKE to match entries. This works when I enter the search criteria in the correct case. “Ha” finds “Hairdresser” only. UCASE and UPPER give me an error that it can not be used with LIKE or have no effect. I use LIKE ‘%’ || [SCcfilter].[Location] || ‘%’
I set the textfield to be matched to “VARCHAR_IGNORECASE” in the table and it works ignoring the case.
But when I now search for “ha” it finds “Hairdresser” and “Shanghai” because Shanghai contains “ha”. Should only find entries starting with “Ha” or “ha”.
Libreoffice 24.2.7.2
Any advice?
1 Like
Leave out the first joker: LIKE [SCcfilter].[Location] || '%'
The % stands for “any characters or none”. If the expression starts with %, anything before [SCcfilter].[Location] will match as well.
With column type VARCHAR:
... WHERE UPPER("X") LIKE UPPER( [SCcfilter].[Location] || '%' )
1 Like
Thank you, the suggestion worked. Before I left out the first joker but not the two vertical lines. Moreover, with this code is UPPER working which I had tried in the past without success.
Merry Christmas
The two vertical lines are the concatenation operator. || means the same as & in a spreadsheet. It concatenates 2 strings to the left and right of the operator. So there must be something left and right of the operator.