[Solved] Query Base for character in the column

Creating tables and queries
Post Reply
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

[Solved] Query Base for character in the column

Post by cub001 »

Is it possible to Query a column of single words for a specific character?
For example, I have a database with a column of single words. Each word has one specific number (ex: word3, word4, etc) and only one number per word.
What I would like to do is to query that column for a specific number. I also want to be able to update that query with other
specific numbers without building more queries.
Any help would be appreciated. :)
Thanks
cub001
Last edited by Hagar Delest on Mon Dec 03, 2018 9:17 am, edited 1 time in total.
Reason: tagged solved
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Post by FJCC »

You can write a query like

Code: Select all

SELECT * FROM "TableName" WHERE "WORD" LIKE '%' || :NUMBER || '%'
Substitute your table name and column name for "TableName" an "WORD". The % serve as wildcards and the || concatenate text. You will be prompted for a number each time you run the query. If the numbers are always at the end of the word, you can omit the trailing || '%'.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Post by cub001 »

Talk about getting excited. Unfortunately, I can't get it to work.
Let me tell you what I have done so far, I probably did it wrong.
I have a CSV file that I created to build a database from. It has these rows:
"FREQ";"CALL";"MODE";"NAME";"QSO_DATE";"QSO_DATE_OFF";"TIME_OFF";"TIME_ON";"RST_RCVD";"RST_SENT";"STATE";"BAND"
The base built fine but I discovered that it does not convert it to an editable base from the spreadsheet made, that is able to add records. For example: if there are 100 records then you have a base that has 100 records that one can work with, but cannot add records.
So I went back to the SS and added the original records plus blank records to 1000 records. This way I can add more records.
Now I wanted to query the CALL field. Couldn't make it work and came here. I thought sure yours would work but I get:" the query too complex"
FYI: My table is already built "Sheet1" and cannot rename. This is "Sheet1". The base is called MyLog.odb
I built the Quert as you described.

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER
Am I doing something wrong?
Thanks so much for your time and help
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Post by FJCC »

Try this form

Code: Select all

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Post by cub001 »

Well sorry to report Still have issues.
I don't get the error message, however, all I do get is the Header field names:
"FREQ";"CALL";"MODE";"NAME";"QSO_DATE";"QSO_DATE_OFF";"TIME_OFF";"TIME_ON";"RST_RCVD";"RST_SENT";"STATE";"BAND", across the top. That part looks great. But that's all I get.
No records what so ever. I am still using 415 but don't think that's the problem. Also tried other similar SQL statements all I could make it do is display the top field names.
For some reason, it's not recognizing the individual fields, much less parsing the word That I really don't understand because the Spreadsheet and Dbase are so closely related.
Thanks for your continued support
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query Base for char. in the column

Post by robleyd »

For some reason, it's not recognizing the individual fields
Or more likely, the query is not returning any results. What happens with a simple query like SELECT * FROM "YourTableName" ?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Post by cub001 »

well, that's encouraging it works great. Didn't think to try that.
Wonder what the heck is going on with the other 2 queries...

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER)

Thanks
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Post by FJCC »

It is probably easiest if you upload your spreadsheet. To do that, click Post Reply and look for the upload attachment tab below the box where you type a response.
You can also try

Code: Select all

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%')
If that works, it means you have characters after the numbers, probably unintended spaces.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Post by cub001 »

Ok.
I am uploading both a test CSV file that the spreadsheet was created with and the Spreadsheet file.
Please note that (Separator Symbols) were changed so that OO would convert it properly.
Also, Please note that I extended the ID field to 25 so you can sorta play around with that because I could not get it to
Convert to a base that one could add records too. I had to basically add blank records...

Hope I did the upload right??
Thanks
Attachments
log1.csv
Original CSV file before conversion
(1.23 KiB) Downloaded 231 times
MylogTest.ods
Spreadsheet file converted file CSV file
(14.09 KiB) Downloaded 223 times
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Query Base for char. in the column

Post by FJCC »

This works for me with your spreadsheet.

Code: Select all

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%')
The numbers are in the middle of the CALL text, so you need a % before and after :NUMBER.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: Query Base for char. in the column

Post by cub001 »

BINGO..
Would you please explain to me "LIKE CONAT( '%', : NUMBER, '%' )" in detail... I thought I put that exact statement in..Sorry I guess I didn't
Thank you so much. I have been trying everything..
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Post by FJCC »

The LIKE token tells the database that the comparison between the thing on the left and on the right will include some wildcard matching. That is, the query is not looking for an exact match but for text that contains certain features.

CONCAT() just glues together text.
CONCAT('A', 'piece of', 'text')
would return Apiece oftext. Of course, it is usually used with column names instead of literal text and then the content of the column in the selected row is glued to whatever else is passed to CONCAT(). CONCAT( '%', : NUMBER, '%' ) would seem to return %:NUMBER% but Base passes the query through an interpreter and that takes a text that starts with a colon to mean "ask the user for input". So, :NUMBER results in Base asking for a user input called NUMBER. After you input that, the CONCAT() continues using your input. If you response to the request for NUMBER with a 5, CONCAT('%', :NUMBER, '%') results in '%5%'.

So now we know that
"CALL" LIKE CONCAT( '%', : NUMBER, '%' )
will be processed as
"CALL" LIKE '%5%'
The % signs are wild cards that mean "match any text". %5% means "match any text, followed by 5, followed by any text" which is to say "match a text as long as it contains a 5".
WHERE "CALL" LIKE '%5%'
will select any row where the CALL column contains a 5.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Post by cub001 »

well, I figured out why I was not querying properly..
I changed the script from SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%',:NUMBER, '%') To SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

Why won't both work???? :crazy:

Thank you so much for your time and explanations
cub001
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Post by FJCC »

To add to your confusion, I ran

Code: Select all

SELECT "CALL" FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%', :NUMBER, '%')
using your spreadsheet as the table and it worked fine.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Post by cub001 »

Well, this one...SELECT "CALL" FROM "Sheet1" WHERE "CALL" LIKE CONCAT( '%', :NUMBER, '%' ) does not work in mine for some reason..
I would really like to understand why...

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%' does work fine ????

Maybe compiler, program version?????

Thanks
Cub001
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Query Base for character in the column

Post by FJCC »

Strangely,

Code: Select all

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'
does not work for me and

Code: Select all

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%', :NUMBER, '%')
does work. I am on Windows 10 AOO version 4.1.6.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Query Base for character in the column

Post by robleyd »

Does Base provide any debug functionality that would allow seeing the actual query that is passed to the database engine?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Post by cub001 »

FYI:
These two work for me

SELECT * FROM "Sheet1" WHERE "CALL" LIKE '%' || :NUMBER || '%'

SELECT * FROM "Sheet1" WHERE "CALL" LIKE CONCAT('%' || :NUMBER || '%')

Apparently, the || operators work best with CONAT...
However, that's all that I can get to work when trying to get the same results.
Merry Christmas and Thanks Again
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: [Solved] Query Base for character in the column

Post by eremmel »

Did some testing:
# Data based on Calc document
AOO 4.1.4 only concat('%', :letter, '%') with || query too complex
LO 5.2.5.1 only concat('%', :letter, '%') with || query too complex
LO 6.0.5.2 only concat('%', :letter, '%') with || query too complex

# Data based on HSQLDB 1.8 (Embedded database)
AOO 4.1.4 supports concat( '%', concat(:letter, '%'))
and '%' || :letter || '%'
but not concat('%', :letter, '%') Java error.

So it looks like the access to Calc has its own program path or database driver...
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Query Base for character in the column

Post by Villeroy »

When it comes to pseudo-databases (sheet, text, dBase), http://www.openoffice.org/dba/specifica ... tions.html lists all availlable functions.
In early versions of Base, the || operator was not availlable at all and CONCAT was limited to 2 arguments which led to absurdities as CONCAT(arg1, CONCAT(arg2, CONCAT(arg3, '%')))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
cub001
Posts: 27
Joined: Thu Sep 27, 2012 9:29 pm
Location: Metropolis IL Hometown of SUPERMAN WB7PNC

Re: [Solved] Query Base for character in the column

Post by cub001 »

Well, it has been enlightening for sure for me.
Still, In my opinion, one can't beat OO suite for an opensource bundle.
I have both Libre and OO for years, mainly because of the ease at which one can save and export in Libre, that OO writer still doesn't have.
But the little bit we accomplished in this effort has answered my issues about querying a column for characters and data.
I really appreciate the kindly help...I'm reading through MySQL, PostgreSQL, etc trying to find some correlations.
I use to program a lot and totally understand how compatibility and hard-headedness affect a programmer's code.
Sorta like one can't be everything to everyone..
Thanks Much!!
Cub
Apache
OpenOffice Suite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
Post Reply