Ask Your Question

Extracting Mid text in a query

asked 2018-12-02 03:00:30 +0200

Mycle gravatar image

updated 2018-12-02 03:17:36 +0200

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.


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-12-02 05:19:49 +0200

Mycle gravatar image

HSQLDB embedded, Noted for the future Ratslinger

Worked perfectly! Thank you, very much appreciated.


edit flag offensive delete link more

answered 2018-12-02 03:49:26 +0200

Ratslinger gravatar image


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

edit flag offensive delete link more


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

Ratslinger gravatar imageRatslinger ( 2018-12-03 01:31:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-02 03:00:30 +0200

Seen: 192 times

Last updated: Dec 02 '18