Extracting Mid text in a query

I have a table containing Caravan make, model and owner details. The ‘Model’ text field contains the caravan model details such as MR18ERS2, MR22BES, MR19ES, MR16ER etc. I want to use a query field to extract the caravan length (characters in position 3 and 4 of the string) Tried using MID( [Model], 3, 2) without success. Hoping someone can help.

Mike

Hello,

When dealing with Base queries, it is helpful to know what database is being used. This can be default HSQLDB embedded, experimental Firebird embedded or external such as MySQL or PostgreSQL. You should also specify OS & LO version being used as occasionally bugs are associated with specifics.

If dealing with HSQLDB embedded you can use substring function. Syntax:

SUBSTRING(string, start, length)

So you need something like:

Select substring("YOUR_FIELD", 3, 2) AS "ExtractedString" From "YOUR_TABLE"

So if YOUR_FIELD value is ‘MR18ERS2’ then ExtractedString will be ‘18’.

As you have been helped, please help others to know the question has been answered by clicking on the :heavy_check_mark: in upper left area of answer which satisfied the question.

HSQLDB embedded, Noted for the future Ratslinger

Worked perfectly! Thank you, very much appreciated.

Mike