Ask Your Question

Help with Query Excluding empty table data fields

asked 2018-12-03 08:09:34 +0100

Mycle gravatar image

updated 2018-12-03 11:13:13 +0100

Following a previous question which was kindly answered by @Ratslinger.
I have a Table "tblCaravans" one of the table text fields "Model" contains the caravan model which includes the length of the caravan in ft. ie: "MR18ES2", "MR19BES", "MR16ER" etc. The text position 3 and 4 contains the length of the caravan in ft.
I needed to report on the van length by extracting the text from this field's pos 3 and 4 and had tried using SUBSTRING in a Query without success.

Thanks to @Ratslinger who provided the SQL:

SELECT "Length", COUNT( "Length" ) FROM ( SELECT SUBSTRING ( "Model", 3, 2 ) "Length" FROM "tblCaravans" ) GROUP BY "Length" ORDER BY "Length" ASC

which workes fine............HOWEVER!

Some of the entries don't contain data in the "Model" field as members may not have taken delivery or may be changing model. This results in a blank field in the query results.

For my report how can I exclude blank entries OR substitute text such as "???" or "N/R" for not recorded for the "Length" for the report.

At Present this is my query result, first cell blank:

image description

Here's hoping someone has an answer.


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-12-03 18:46:45 +0100

Ratslinger gravatar image


The query can be modified but it depends upon the result actually wanted. If you wish to eliminate those items without a "Length" then:

SELECT "Length", COUNT( "Length" ) FROM ( SELECT SUBSTRING ( "Model", 3, 2 )  "Length" FROM "tbl_Caravans") Where "Length" <> '' GROUP BY "Length" ORDER BY "Length" ASC

If you want to include this in the report then something like:

SELECT "Length", COUNT( "Length" ) FROM ( SELECT coalesce(SUBSTRING ( "Model", 3, 2 ), 'N/R') "Length" FROM "tbl_Caravans") GROUP BY "Length" ORDER BY "Length" ASC
edit flag offensive delete link more


Yahoo! Perfect @Ratslinger Plus I have learned something. Thank you!


Mycle gravatar imageMycle ( 2018-12-03 23:16:09 +0100 )edit

Glad it suits your needs. Seems what you can really use is more knowledge of SQL. For that you can refer to the DB manual. For HSQL v1.8 -> Hsqldb User Guide. There are also multiple on line sites for reference for various DB's (but not always applicable to HSQL).

Ratslinger gravatar imageRatslinger ( 2018-12-03 23:24:21 +0100 )edit

Thanks again @Ratslinger


Mycle gravatar imageMycle ( 2018-12-04 09:55:17 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-03 08:09:34 +0100

Seen: 28 times

Last updated: Dec 03 '18