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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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
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.
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...
Cub Apache OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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????
Thank you so much for your time and explanations
cub001
Cub Apache OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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.
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.
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)
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
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 OpenOfficeSuite 4.1.6
Ask not what you can do for the computer, but what the computer can do for you.
................................................................................... Roberto C. Benitez.