I just need to obtain all distinct color entries but with the detail that "Colors" may have a color for each line. Char(10) as line change.So i want to query and obtain all results that are distinct but with that kind of read each line on "colors".By the moment i have no idea how can i do that from SQL....

I got that wrong last night. GROUP_CONCAT (HSQL2) concatenates separated strings to concatenated strings. With a little help of Calc you may be able to normalize your data. Dump the strings, split in rows, transpose rows to columns, merge columns eliminate duplicates, paste into prepared database table with PK, generate intermediate table for n-m-relation. Compose an SQL statement which dumps all PKs of single words into the intermediate table having a matching substring in the old field together with the PKs of the other table. This takes some time and effort.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

finally i did figure how to split all results with the char(10) as line separator. The problem, 'Distinct' only works with a single column. So, if we have a result with 4 columns, just a line on each, is there a way to use 'Distintct' in order to have a final result in a single column with all the diferent terms on the 4 ?@UnklDonald418 you didnt understand the problem...@Villeroy i use base, not calc.

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.

In order to turn this into a many-to-many relation:Create a table, say "REMARKS" (text, auto-id) and a linking table, say "DATA_REM" (2 integers as primary key) storing which remarks belong to which data record.Create a view to SELECT DISTINCT "S1" FROM "View2", copy the view and paste the S1 field into the remarks table.Create another view to select the remark-IDs and the corresponding data-IDs based on the common strings in the remarks and View2:

i'm trying to understand what you did there exactly, because at the momenti didnt get it. L1 ? L2 ? etc etc explain please. My data source come from a single column, lines separed by char(10). And i want to split them in 4 results, because 4 lines, and at finally do 'distinct' on the 4 obtained columns and obtain a new column with the distinct results on it.

Villeroy, yes, your first query is very intuitive. I got some near result but still persist the last problem i am having. Just having the 4 results in 4 columns, we have to do a last checking using 'distinct' and obtain a final single column with the distinct results from the 4 columns... I see that is a problem now....

Hi,a couple of days after first browsing this topic i had a vague recollection of having tackled this issue before.sure enough after having searched through some very old databases i found what i was looking for.

from memory i attempted a solution using the HSQL 1.8.0.10 functions 'LOCATE' & 'SUBSTR' as per Villeroy.it's easy enough to extract substrings this way when they are small in number & we know the max number there may be.unfortunately if we have more than 4 or 5 substrings the SQL becomes messy & difficult to read.even if the substrings are successfully extracted we still have a problem in selecting distinct values & storing the results.i concluded that using SQL alone was not a practical solution so wrote a macro which solved the issue.

to clarify:we have a table field which contains a string of text.the text contains substrings which are delimited using a separator.if the separator was a comma then the string could look like this: 'Bill,Bob,Barry,Bartholomew,How Now Brown Cow'.the separator can be any printable character. the OP uses char(10) (line feed). the data was likely input using a form with a multi line text box (as in the attachment).

how to use:1) open the attachment. it contains one table, one form & one macro.2) hit 'Tables' icon.3) hit (Alt+F11) or menu:Tools>Macros>Organise Macros>Open Office Basic.4) expand 'SplitString.odb'.5) expand 'Standard'.6) hit 'Module1'.7) hit 'Edit'.8) from keyboard hit 'F5' (execute).9) from 'Base' main window hit>>> menu:View>Refresh Tables (only necessary when the created tables are not visible).two newly created tables should now be visible.

to extract from your own tables:i think it's best to drag your table and drop it into this database.go to the macro & edit the following as necessary: 'sSeparator', 'sSourceTableName', 'sSourceFieldName'.

just adding a last char(10). Then L2 give the good S2 string start. And i have seen no useful the ABS() usage... ?

Also Villeroy, your example gave me the way to use that table joins to again obtain a single column from 4 as data source, thx for such example But getting deep on it, still there are some duplicates, and i cant understand why. Maybe because the same string come from non S1 column ? but even using 'distinct', that seems there is no needed in your example, doesnt change the fact that in the final results column, appears some duplicates.....

And also Villeroy, why you did use the table view instead of a single query ? i guess you did it to dont have to repeat several times parts of the code, please confirm.

chrisb, thx for your example but, in this case the wanted is a single SQL query to obtain all distinct values. In SQL the table joins skills are needed to achieve it. In basic is it easy...