Apache OpenOffice Community Forum

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?

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 *]

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?

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

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

   SELECT 
      "Name" 
   FROM "Data" 
   WHERE "Name" NOT LIKE '\*%' ESCAPE '\*'

2. Query using SUBSTR function in WHERE
   

   Code: Select all

   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.

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.

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

   SELECT 
      `Name` 
   FROM `Data` 
   WHERE MID(`Name`,1,1) <> '*'
   

2. Code: Select all

   SELECT 
      `Name` 
   FROM `Data` 
   WHERE LEFT(`Name`,1) <> '*'
   

3. Code: Select all

   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.

Thank you for your patience, this has resolved my issue. I will try to be more concise with my questions in the future.