[Solved] Filter out a field that contains an asterisk?
-
- Posts: 10
- Joined: Sat Oct 17, 2015 10:46 pm
[Solved] Filter out a field that contains an asterisk?
OOo 4.0.1 on Windows 7. How do I filter records out of a report that have a field that contains a special character, an asterisk?
Last edited by rabogardus on Tue Oct 20, 2015 5:51 pm, edited 1 time in total.
OpenOffice version 4.0.1
Operating system Windows 7
Operating system Windows 7
Re: how do I filter out a field that contains an asterisk?
I found the answer in the F1-help on Base, query design.
Matching field values starting with **Note:
Alternatively, you may be happy with:
WHERE LOCATE('**Note:', "Text")=1 [above example, begins with **Note:]
WHERE LOCATE('*', "Text")>0 [matches * anywhere]
WHERE LOCATE('*', "Text")=LENGTH("Text") [ends with *]
Matching field values starting with **Note:
Code: Select all
SELECT * FROM "somewhere"
WHERE "Text" LIKE {escape '*'}{escape '*'} || 'Note:*'
WHERE LOCATE('**Note:', "Text")=1 [above example, begins with **Note:]
WHERE LOCATE('*', "Text")>0 [matches * anywhere]
WHERE LOCATE('*', "Text")=LENGTH("Text") [ends with *]
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 10
- Joined: Sat Oct 17, 2015 10:46 pm
Re: how do I filter out a field that contains an asterisk?
Sorry, Villeroy, this did not seem to work but thanks for the help. Let me be a little more specific. I would like to filter out any value that starts with an asterisk in a text field called Name in table Data. SQL looks like this:
SELECT 'Name' FROM 'Data' WHERE 'Name' not like {escape '*'} || %
This results in "Syntax error in SQL expression".
I have also tried:
SELECT `Name` FROM `Data` WHERE `Name` not like {escape '*'}
and got another "Syntax error in SQL expression".
I am of the understanding that the || is to concatenate and the % is to represent anything that may follow the *. I could not find LOCATE in help so I am assuming it is not available to me.
What am I doing wrong?
SELECT 'Name' FROM 'Data' WHERE 'Name' not like {escape '*'} || %
This results in "Syntax error in SQL expression".
I have also tried:
SELECT `Name` FROM `Data` WHERE `Name` not like {escape '*'}
and got another "Syntax error in SQL expression".
I am of the understanding that the || is to concatenate and the % is to represent anything that may follow the *. I could not find LOCATE in help so I am assuming it is not available to me.
What am I doing wrong?
OpenOffice version 4.0.1
Operating system Windows 7
Operating system Windows 7
Re: how do I filter out a field that contains an asterisk?
rabogardus:
Welcome to the OpenOffice / LibreOffice Base forum.
You said / asked ( I only changed your SQL Query by adding some line feeds to make it easier to read ):
You could try the code below, or even the second one, to accomplish your task:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Welcome to the OpenOffice / LibreOffice Base forum.
You said / asked ( I only changed your SQL Query by adding some line feeds to make it easier to read ):
You did NOT indicate which database back-end you are using. I am assuming, it is HSQL ( either the Embedded version ( 1.8.0.10 ) or, HSQL 2.3 - a later superior version ).rabogardus wrote:
I would like to filter out any value that starts with an asterisk in a text field called Name in table Data. SQL looks like this:
Code: Select all
SELECT 'Name' FROM 'Data' WHERE 'Name' not like {escape '*'} || %
You could try the code below, or even the second one, to accomplish your task:
- Query using NOT LIKE in WHERE
Code: Select all
SELECT "Name" FROM "Data" WHERE "Name" NOT LIKE '\*%' ESCAPE '\*'
- Query using SUBSTR function in WHERE
Code: Select all
SELECT "Name" FROM "Data" WHERE SUBSTR("Name",1,1) <> '*'
- Note that both the database table names and column names are surrounded by double quotes ( " ) while text strings are surrounded by single quotes ( ' )
- In the first Query, the ESCAPE character, an asterisk, is defined by including the word ESCAPE as a text string and it is defined as '\*' . . . and . . . in the NOT LIKE portion, it is coded as: '\*%' .
- In the second Query, it just gets a substring using the function SUBSTR starting at the first character for a length of 1, and, have it return all records where it does NOT ( <> ) start with '*' .
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 10
- Joined: Sat Oct 17, 2015 10:46 pm
Re: how do I filter out a field that contains an asterisk?
Sorry for not listing the back-end database before, it is MS Access (version unknown, but prior to MS Access 2007). I tried both your suggestions. The first resulted in the following error message:
"The data content could not be loaded."
Associated information:
SQL Status: 3000
Error code: -2147467259
The SQL command leading to this error is:
SELECT `Name` FROM `Data` WHERE NOT `Name` LIKE '\*%' ESCAPE '\*'
The SUBSTR function was not available in Query Design so could not be used.
When I tried specifying criterion NOT LIKE 'Bo*' in Query Design the following SQL was genned:
SELECT `Name` FROM `Data` WHERE NOT `Name` LIKE 'Bo%'
OO appeared to change the * to % as I did code the * not the %, but it did produce the correct results, no names starting with Bo were in the results.
Thank you in advance for any assistance you can offer.
"The data content could not be loaded."
Associated information:
SQL Status: 3000
Error code: -2147467259
The SQL command leading to this error is:
SELECT `Name` FROM `Data` WHERE NOT `Name` LIKE '\*%' ESCAPE '\*'
The SUBSTR function was not available in Query Design so could not be used.
When I tried specifying criterion NOT LIKE 'Bo*' in Query Design the following SQL was genned:
SELECT `Name` FROM `Data` WHERE NOT `Name` LIKE 'Bo%'
OO appeared to change the * to % as I did code the * not the %, but it did produce the correct results, no names starting with Bo were in the results.
Thank you in advance for any assistance you can offer.
OpenOffice version 4.0.1
Operating system Windows 7
Operating system Windows 7
Re: how do I filter out a field that contains an asterisk?
OK, now that I / we know your database back-end is Microsoft Access ( which does NOT follow the SQL standards ) I suggest you try ANY OF the following Queries to accomplish your task:rabogardus wrote:Sorry for not listing the back-end database before, it is MS Access (version unknown, but prior to MS Access 2007).
Code: Select all
SELECT `Name` FROM `Data` WHERE MID(`Name`,1,1) <> '*'
Code: Select all
SELECT `Name` FROM `Data` WHERE LEFT(`Name`,1) <> '*'
Code: Select all
Select `Name From `Data` Where `Name` NOT LIKE ('*%')
- In the first query above, the Access function MID works just like the function I described above - SUBSTR ( Substring ) . . . so, it returns the contents of `Name` starting with the first character for a length of 1. And, the <> in words is equivalent to NOT EQUAL.
- In the second query above, the Access function LEFT is used to return the FIRST leftmost character from `Name` when it is NOT EQUAL to the text string '*'
- In the third query above, using NOT LIKE and in the text string - surrounded by parentheses - ('*%')
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 10
- Joined: Sat Oct 17, 2015 10:46 pm
Re: how do I filter out a field that contains an asterisk?
Thank you for your patience, this has resolved my issue. I will try to be more concise with my questions in the future.
OpenOffice version 4.0.1
Operating system Windows 7
Operating system Windows 7