arfgh wrote:@Villeroy i use base, not calc.
Calc can be a helpful tool to import, manipulate and paste back database data in more visual ways.
With HSQL2 you would have built-in functions for arrays and regular expressions.
With HSQL1 I played with a table "Data" having a primary key "ID" and a multi-line comment field "REM":
Query1 (direct SQL, to be stored as "View1"):
Code: Select all
SELECT
casewhen("L1" > 0, SUBSTR( "REM", 1, "L1"-1), "REM" ) AS "S1",
casewhen("L1" > 0, SUBSTR( "REM", "L1" +1, ABS("L2" - "L1")), '' ) AS "S2",
casewhen("L1">0 AND "L2">0, SUBSTR( "REM", "L2" +1, ABS("L3" - "L2")), '') AS "S3",
casewhen("L1">0 AND "L2">0 AND "L3">0, SUBSTR( "REM", "L3" +1), '') AS "S4",
"ID","REM",L1,L2,L3
FROM (
SELECT
LOCATE( CHAR( 10 ), "REM" ) AS "L1",
LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM" ) +1) AS "L2",
LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM", LOCATE( CHAR( 10 ), "REM" ) +1) +1) AS "L3",
"ID", "REM"
FROM "Data"
) AS "L"
The inner SELECT selects 3 numbers L1,L2,L3 into record set "L" marking the positions of up to 3 times CHAR(10).
The outer SELECT selects up to 4 substrings S1,S2,S3,S4 based on the positions L1,L2,L3 of record set L.
With Windows line breaks the query needs some modification since Windows line breaks consist of 2 characters.
Query2 (direct SQL referring to "View1"):
Code: Select all
SELECT "S"."S1", "S"."ID" FROM (
SELECT "S1", "ID" FROM "View1"
UNION SELECT "S2", "ID" FROM "View1"
UNION SELECT "S3", "ID" FROM "View1"
UNION SELECT "S4", "ID" FROM "View1"
) AS "S"
WHERE COALESCE ( RTRIM( LTRIM( "S1" ) ), '' ) != ''
This returns unique combinations of strings and record IDs without empty strings nor blank strings.