[SOLVED] Search part of a title?

Discuss the database features

[SOLVED] Search part of a title?

Postby Kirjava » Sat Nov 16, 2019 5:38 pm

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.
Last edited by floris v on Sat Nov 16, 2019 11:56 pm, edited 2 times in total.
Reason: Added Solved icon, floris v, moderator
OpenOffice 3.1 on Windows 7
Kirjava
 
Posts: 20
Joined: Sun May 03, 2015 2:58 pm

Re: Search part of a title?

Postby Villeroy » Sat Nov 16, 2019 6:10 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27381
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search part of a title?

Postby Kirjava » Sat Nov 16, 2019 11:12 pm

Thank you. The first one did exactly what I want.
OpenOffice 3.1 on Windows 7
Kirjava
 
Posts: 20
Joined: Sun May 03, 2015 2:58 pm


Return to Base

Who is online

Users browsing this forum: Google [Bot], MSN [Bot] and 9 guests