[Solved] Filter out a field that contains an asterisk?

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Filter out a field that contains an asterisk?

Postby rabogardus » Sat Oct 17, 2015 11:15 pm

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
rabogardus
 
Posts: 10
Joined: Sat Oct 17, 2015 10:46 pm

Re: how do I filter out a field that contains an asterisk?

Postby Villeroy » Sun Oct 18, 2015 5:46 pm

I found the answer in the F1-help on Base, query design.
Matching field values starting with **Note:
Code: Select all   Expand viewCollapse view
SELECT * FROM "somewhere"
WHERE "Text" LIKE {escape '*'}{escape '*'} || '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 *]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24659
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: how do I filter out a field that contains an asterisk?

Postby rabogardus » Mon Oct 19, 2015 12:24 am

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? :crazy:
OpenOffice version 4.0.1
Operating system Windows 7
rabogardus
 
Posts: 10
Joined: Sat Oct 17, 2015 10:46 pm

Re: how do I filter out a field that contains an asterisk?

Postby Sliderule » Mon Oct 19, 2015 5:12 am

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 ;) ):

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   Expand viewCollapse view
SELECT
   'Name'
FROM 'Data'
WHERE 'Name' not like {escape '*'} || %



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

You could try the code below, or even the second one, to accomplish your task:

  1. Query using NOT LIKE in WHERE

    Code: Select all   Expand viewCollapse view
    SELECT
       "Name"
    FROM "Data"
    WHERE "Name" NOT LIKE '\*%' ESCAPE '\*'

  2. Query using SUBSTR function in WHERE

    Code: Select all   Expand viewCollapse view
    SELECT
       "Name"
    FROM "Data"
    WHERE SUBSTR("Name",1,1) <> '*'
Explanation:
  1. Note that both the database table names and column names are surrounded by double quotes ( " ) while text strings are surrounded by single quotes ( ' )
  2. 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: '\*%' .
  3. 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 '*' .
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1100
Joined: Thu Nov 29, 2007 9:46 am

Re: how do I filter out a field that contains an asterisk?

Postby rabogardus » Mon Oct 19, 2015 10:43 pm

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.
OpenOffice version 4.0.1
Operating system Windows 7
rabogardus
 
Posts: 10
Joined: Sat Oct 17, 2015 10:46 pm

Re: how do I filter out a field that contains an asterisk?

Postby Sliderule » Tue Oct 20, 2015 5:19 am

rabogardus wrote:Sorry for not listing the back-end database before, it is MS Access (version unknown, but prior to MS Access 2007).

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:

  1. Code: Select all   Expand viewCollapse view
    SELECT
       `Name`
    FROM `Data`
    WHERE MID(`Name`,1,1) <> '*'

  2. Code: Select all   Expand viewCollapse view
    SELECT
       `Name`
    FROM `Data`
    WHERE LEFT(`Name`,1) <> '*'

  3. Code: Select all   Expand viewCollapse view
    Select
         `Name
    From `Data`
    Where `Name` NOT LIKE ('*%')
Explanation:
  1. 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.

  2. 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 '*'

  3. In the third query above, using NOT LIKE and in the text string - surrounded by parentheses - ('*%')
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1100
Joined: Thu Nov 29, 2007 9:46 am

Re: how do I filter out a field that contains an asterisk?

Postby rabogardus » Tue Oct 20, 2015 5:53 pm

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
rabogardus
 
Posts: 10
Joined: Sat Oct 17, 2015 10:46 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 3 guests