[Solved] Query Base for character in the column

[Hagar Delest]

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

[FJCC]

You can write a query like

Code: Select all   Expand viewCollapse view

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 || '%'.

[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

[FJCC]

Try this form

Code: Select all   Expand viewCollapse view

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

[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

[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" ?

[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

[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   Expand viewCollapse view

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

If that works, it means you have characters after the numbers, probably unintended spaces.

[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

[FJCC]

This works for me with your spreadsheet.

Code: Select all   Expand viewCollapse view

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.

[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..

[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.

[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????

Thank you so much for your time and explanations
cub001

[FJCC]

To add to your confusion, I ran

Code: Select all   Expand viewCollapse view

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

using your spreadsheet as the table and it worked fine.

[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

[FJCC]

Strangely,

Code: Select all   Expand viewCollapse view

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

does not work for me and

Code: Select all   Expand viewCollapse view

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

does work. I am on Windows 10 AOO version 4.1.6.

[robleyd]

Does Base provide any debug functionality that would allow seeing the actual query that is passed to the database engine?

[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

[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...

[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, '%')))

[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!!