1

I have two database columns (datatype varchar) which consists values like below

column 1 
--------
1.8.0
1.7.0
9.0
10.0

column 2
--------
121
65
78

On UI I have to merge these two columns and show values like 

1.8.0_121
1.8.0_78

The problem is while sorting this combined column shown on UI. Since both the columns are varchar in db order by is happening using string and 121 is coming before 78 which is wrong for ascending order.

As a solution I have used ABS for mysql and to_number for oracle on these columns. MySQL is working fine for both the columns but Oracle to_number is throwing error "Invalid number" for first column since its having value as 1.8.0

Kindly suggest how to deal with this situation using code changes only. As doing database schema change will lead to lot of changes at many systems so don't want to touch that.

Sample table with data

Sample table with data

Query with order by clause

SELECT (MAJOR || '_' || MINOR) AS VER 
FROM VERSION 
ORDER BY MAJOR ASC, MINOR ASC

Results

Results

Corrected query for considering varchar columns as numbers 

SELECT (MAJOR || '_' || MINOR) AS VER 
FROM VERSION 
ORDER BY MAJOR ASC, TO_NUMBER(MINOR) ASC

Corrected results

Correct Results

Till this point its perfectly fine. But the issue is MAJOR column can have values like 9.0 and 10.0 so i want to convert major column also to_number in order by clause so that sorting is proper. But since the values are 1.8.0 its throwing error.

The case which is having issue is

Erroneous results

Improve this question
7
  • Please edit your question and show us sample data in tabular form, as we would see when looking at MySQL Workbench or Oracle studio. Then, show the exact output your expect, along with any query you have tried. Commented Aug 17, 2018 at 7:51
  • you can try to order by order by lpad(column,3) or to_number(column) Commented Aug 17, 2018 at 8:05
  • @hotfix, TO_NUMBER won't work (as the OP has said, it raises invalid number). Commented Aug 17, 2018 at 8:15
  • @TimBiegeleisen I have updated the question with some more details. Commented Aug 17, 2018 at 8:18
  • @Littlefoot you're right, have not read to the end Commented Aug 17, 2018 at 8:20

3 Answers 3

Reset to default
2

One option is to extract all numeric pieces (using regular expressions; it is the simplest option, I'd say), apply TO_NUMBER to them and sort by those values, e.g. 

SQL> with test (col) as
  2    (select '1.8.0' from dual union
  3     select '1.7.0' from dual union
  4     select '9.0'   from dual union
  5     select '10.0'  from dual
  6    )
  7  select col
  8  from test
  9  order by to_number(regexp_substr(col, '\d+', 1, 1)),
 10           to_number(regexp_substr(col, '\d+', 1, 2)),
 11           to_number(regexp_substr(col, '\d+', 1, 3));

COL
-----
1.7.0
1.8.0
9.0
10.0

SQL>
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your reply. This approach works if I write this on my SQL Developer. But doesn't seem feasible code wise due to following: 1. All queries are generated using java code like dynamic queries so it will be difficult to append this clause everywhere. 2. The list of major version is long and it keeps on changing every now and then so code also needs to be changed. It wont be a robust approach.
You're welcome. Well, that's how it is. You'll have to sacrifice something to get another thing. Question is: what will it be. See if you can change the way you enumerate version and include additional character, such as 01.07.00, 09.00, 10.01 etc. That might work and you wouldn't even have to change anything else in your code.
1

This query works for me.

SELECT ( major 
     || '_' 
     || minor ) AS ver 
FROM   
(SELECT major, minor, 
    To_number(Replace(Decode(Substr(major, 0, 2), '1.',Substr(major, 3), major), '.', '')) 
      AS major_num, 
    To_number(Decode(Instr(minor, '-'), 0, minor,Substr(minor, 0, Instr(minor, '-') - 1))) 
      AS minor_num 
FROM   version) 
ORDER  BY major_num, 
      minor_num;
Improve this answer

Comments

0

Here's a hack...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(column1 VARCHAR(12) NOT NULL
,column2 DECIMAL(5,2) NULL
);


INSERT INTO my_table VALUES
('1.8.0',121),
('1.7.0',65),
('9.0',78),
('10.0',NULL);

SELECT a.column1
     , b.column2 
  FROM 
     (SELECT column1,@i:=@i+1 i FROM my_table,(SELECT @i:=0) vars ORDER BY INET_ATON(column1))a
  JOIN
     (SELECT column2,@j:=@j+1 j FROM my_table,(SELECT @j:=0) vars ORDER BY ISNULL(column2),column2)b
    ON b.j = a.i;
+---------+---------+
| column1 | column2 |
+---------+---------+
| 1.7.0   |   65.00 |
| 1.8.0   |   78.00 |
| 9.0     |  121.00 |
| 10.0    |    NULL |
+---------+---------+
Improve this answer

5 Comments

Thanks for your response. However as already mentioned I cant change the database schema as its used at many other places in code. It will be a lot of code and script changes. So I want to handle it using code only.
Its changing the datatype of the second column. It should be varchar because sometimes we save strings also there.
Just change ISNULL(column2),column2 to ISNULL(column2),column2+0
But you really need to clean up your data. If that means firing management, then so be it.
Data is correct as per the application business logic. Its just that sorting is not working properly. Even for MySQL when I am using ABS with both this columns its just perfect. I am facing issue with Oracle database only. Seems like to_number is too strict.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.