column name sorter issue

[chaithuj]

Hi,

I have same column names in 2 tables , one of it in Lower case and other in Upper case.
I am trying to use sort and map
example -
Table 1 - ST_AD, STAT_C
Table 2 - st_ad, stat_c

When i sort, these 2 columns are coming in different orders, hoe can I overcome this

[takbb]

Hi @chaithuj , When you say “when they’re sorted they are coming in different orders”, what do you mean by that?

A table with all lowercase column names would sort into the same order as a table with all uppercase column names surely? Or is this because of alphabet / characterset differences?

Can you show an example of how the sort order differs?

[chaithuj]

Yeah that is not happening.
The lower cases are sort in a different order ad Upper case are sorted in a different order

[takbb]

Hi @chaithuj

Ok, it is actually doing what it is being asked, but… The problem here is that what we consider to be alphabetic sorting is actually sorting in character code order. The presence of underscores and some other punctuation (for example) affects that sequencing.

The upper case characters have ASCII codes in the range 65 (A) to 90 (Z) and the lower case are in the range 97 (a) to 122 (z).

An underscore has a code of 95, which means it collates AFTER all upper case but BEFORE all lower case.

So this means that “ST_AD” would collate after “STAT” but “st_ad” would collate before “stat”.

To perform like-for-like alphabetic sorting you would need to convert all your column names to all upper case or all lower case first.

You originally said that in one table all your names were upper case, in reality in one table the names are mixed case, so you could never have guaranteed what you might consider pure alphabetic sorting.

For example, suppose you had a column “STat”, this would sort after a column called “STZZ”, since a lower case “a” collates after an uppercase “Z”.

Additionally, given that columns in table 2 are not an exact lower case match of the columns in table 1, it seems to me that relying on alphabetic sorting (even if it worked exactly in a way we might consider “properly” alphabetic) would only really work by chance here.

I’d suggest though that you first make the columns of both tables fully uppercase and then try sorting them to see if it works for you.

If you need assistance with that, the nodes that are likely to be involved have changed names from v4.x to v5.x.

Which version of KNIME are you using?

[chaithuj]

Thank you, we will change the column names and re try