Page 1 of 1

[SOLVED] Search part of a title?

PostPosted: Sat Nov 16, 2019 5:38 pm
by Kirjava
Is there an includes function in SQL?

I already have a query to search for a title with " = :Enter_01_Title"
(for when I know what I want but don't know where it is)

The problem is when there's more than one season of something
or I can't remember the whole title.

I'm guessing I should put something else where "= :Enter" is.
What do I put there?
Is this even something base can do?

I tried google but I couldn't find it.

Re: Search part of a title?

PostPosted: Sat Nov 16, 2019 6:10 pm
by Villeroy
The LIKE operator is for pattern matching.
Code: Select all   Expand viewCollapse view
SELECT ... WHERE "Title" LIKE '%' || :Enter_01_Title || '%'

|| is the concatenation operator, analog to & in a spreadsheet.
Literal strings are written in single quotes.
'%' in SQL is the same as the well known placeholder * (zero or any count of characters).
'_' in SQL is the same as the well known placeholder ? (one character exactly)
Escape character is \, so '\*' matches a literal *.
Documentation on HSQL 1.8 which is the one which is shipped with OpenOffice and embedded in database documents.
If the column is of type VARCHAR_IGNORECASE, the coparison is case insensitive, otherwise you can use UCASE or LCASE on both sides of the comparison:
Code: Select all   Expand viewCollapse view
SELECT ... WHERE CASE("Title") LIKE '%' || UCASE(:Enter_01_Title) || '%'


Return all records if no parameter is given:
Code: Select all   Expand viewCollapse view
SELECT ... WHERE (UCASE("Title") LIKE '%' || UCASE(:Enter_01_Title) || '%' OR :Enter_01_Title IS NULL)


File based databases connected to dBase, spreadsheets or text files don't support the concatenation operator. There is a CONCAT function which accepts only 2 arguments:
Code: Select all   Expand viewCollapse view
SELECT ... WHERE "Title" LIKE CONCAT('%', CONCAT(:Enter_01_Title, '%'))

Documentation on availlable functions for file based databases: http://www.openoffice.org/dba/specifica ... tions.html

Re: Search part of a title?

PostPosted: Sat Nov 16, 2019 11:12 pm
by Kirjava
Thank you. The first one did exactly what I want.