Wildcards in LibreOffice Base

[bob7]

Hello

I was wondering if it is possible to write a query with libreoffice-base in SQL view that uses wildcards to specify a range of values. At the moment the only wildcards I am aware of are _%.

Below is the section of code that I am currently having to use:

SUM(
CASE WHEN
"tbl_Exam-Question-Mark"."Question_Number" LIKE '01' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '02' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '03' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '04' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '05' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '06' OR
"tbl_Exam-Question-Mark"."Question_Number" LIKE '07'
THEN "tbl_Exam-Question-Mark"."Question_Mark" END
),

The ‘01’, ‘02’ etc. are of the datatype VARCHAR

Is there a way to specify a range of values so the query could be compressed to something like:

SUM(
CASE WHEN
"tbl_Exam-Question-Mark"."Question_Number" LIKE '0[1-7]'
THEN "tbl_Exam-Question-Mark"."Question_Mark" END
),

I am using:

macOS Big Sur 11.2.3, LibreOffice 7.1.5.2, Firebird Embedded

Thanks a lot

[mariosv]

For Firebird 3.0 Syntax: SQL Regular Expressions

[Ratslinger]

Hello,

Reference → CASE construct

Try:

SUM(
CASE WHEN
"tbl_Exam-Question-Mark"."Question_Number" <= '07'
THEN "tbl_Exam-Question-Mark"."Question_Mark" END
),

[bob7]

Thanks @Ratslinger

Incidentally, is there an easy way to do syntax highlighting in the posts like you did in your response?

[Ratslinger]

@bob7

syntax highlighting in the posts like you did in your response?

Three back ticks preceding and following the code. For more, please see → Multi-Line Blocks of Code

[bob7]

@Ratslinger

Thanks a lot, I’ll try to remember that for next time.