*Apologies as I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue.
I am trying to compare a table found in two databases.
COLUMN_VALUE 01.00^01.13 01.00 01.12 01.00 01.15
Unfortunately, the table does not have a primary key, so I am trying to order by the same column and compare that way.
When I sort the above table in Oracle, it sorts to:
SELECT * FROM ( SELECT '01.00 01.12' AS "COLUMN_VALUE" FROM DUAL UNION ALL SELECT '01.00^01.13' AS "COLUMN_VALUE" FROM DUAL UNION ALL SELECT '01.00 01.15' AS "COLUMN_VALUE" FROM DUAL) ORDER BY 1; COLUMN_VALUE 01.00 01.12 01.00 01.15 01.00^01.13
However, when I perform the sort in Postgres, it sorts to:
SELECT * FROM ( SELECT '01.00 01.12' AS "COLUMN_VALUE" UNION ALL SELECT '01.00^01.13' AS "COLUMN_VALUE" UNION ALL SELECT '01.00 01.15' AS "COLUMN_VALUE") T1 ORDER BY 1; COLUMN_VALUE 01.00 01.12 01.00^01.13 01.00 01.15
This issue appears to be how the ^ character is sorted. Does anyone know a workaround so that both databases sort the information the same way?
Advertisement
Answer
Creating hash values and using them for ordering seems to be an option.
Test tables (Postgresql / Oracle)
Query (Postgresql) – order by MD5 hash
select c_, md5( c_ ) from test order by 2 ; -- result c_ md5 01.00 01.12 335dca42dedecedc19ba65065a7777ec 01.00^01.13 4e1661df191eb006274be5552a998280 01.00 01.15 c1197d14549263a867fd9850f42b68b1
Query (Oracle) – order by MD5 hash
select c_, standard_hash( c_ , 'MD5' ) from test order by 2 ; C_ STANDARD_HASH(C_,'MD5') 01.00 01.12 0x335DCA42DEDECEDC19BA65065A7777EC 01.00^01.13 0x4E1661DF191EB006274BE5552A998280 01.00 01.15 0xC1197D14549263A867FD9850F42B68B1
If the MD5 is not good enough, you can also use SHA256, which will probably give you a different ordering, but the same order result for both Postgresql and Oracle. Examples on DBfiddle, for Postgresql 12 and Oracle 18c.