Extract year, month and day numbers (1 to 2 digits) from a string (LibreOffice Base)-CodePudding

I am using LibreOffice Base (version 7.3) to create a database. I need to extract year, month and day numbers from such strings that consist Chinese charactors as shown following. I think one Chinese charactor is one charactor but I am not sure.

What I have: 1997年8月12日 1971年10月1日 2001年5月26日 2005年12月29日 2010年2月8日 ...

What I want to achieve:

I need to extract the first 4 digits which are the years;
The numbers between the first charactor (年) and the second charactor(月), which are months from 1 to 12 (1 or 2 digits);
The numbers between the second charactor (月) and the third charactor (日)，which are days in a month from 1 to 31 (1 or 2 digits);

I am a newbie, I tried using combination RIGHT() and LEFT() function or substring() method but I failed because of the fact the numbers of months and days are sometimes 1 digit sometimes 2 digits. I will be very grateful if someone can help. Thank you so much.

CodePudding user response：

Unfortunately, you didn't specify which built-in database you are using, Firebird or HSQLDB.

Database type

You didn't provide your table name and Chinese date field name. Therefore, I will show how this problem is solved for the Firebird database, a table named ChinaTest and a field named ChinaDate.

First, replace the first two characters with a dash, and the last character with an empty string (just delete it). This will be done by the REPLACE() function. Just nest three REPLACE()'s one inside the other:

REPLACE( REPLACE( REPLACE( "ChinaTest"."ChinaDate", '年', '-' ), '月', '-' ), '日', '' )

Now the CAST() function easily converts this string into a "real date":

SELECT CAST( <previous_expression> AS DATE ) "RealDate" FROM "ChinaTest"

When you have a real date, you can use the EXTRACT() function to get any part of it.

The final solution might look like this:

SELECT "RealDate", EXTRACT( YEAR FROM "RealDate" ) "iYear", 
                   EXTRACT( MONTH FROM "RealDate" ) "iMonth", 
                   EXTRACT( DAY FROM "RealDate" ) "iDay" 
    FROM (SELECT CAST( 
                   REPLACE( 
                      REPLACE( 
                         REPLACE( "ChinaTest"."ChinaDate", '年', '-' ), 
                      '月', '-' ), 
                   '日', '' ) 
                  AS DATE ) "RealDate" FROM "ChinaTest")

Result

For a HSQLDB database, the query might look like this:

SELECT
 CAST( REPLACE( REPLACE( REPLACE( "Employess"."CNBirthDate", '年', '-' ), '月', '-' ), '日', '' ) AS DATE ) AS "BDate",
 YEAR( CAST( REPLACE( REPLACE( REPLACE( "Employess"."CNBirthDate", '年', '-' ), '月', '-' ), '日', '' ) AS DATE ) ) AS "BDYear",
 MONTH( CAST( REPLACE( REPLACE( REPLACE( "Employess"."CNBirthDate", '年', '-' ), '月', '-' ), '日', '' ) AS DATE ) ) AS "BDMonth",
 DAY( CAST( REPLACE( REPLACE( REPLACE( "Employess"."CNBirthDate", '年', '-' ), '月', '-' ), '日', '' ) AS DATE ) ) AS "BDDay" 
FROM "Employess"

HSQLDB