Skip to Main Content
  • Questions
  • Order by - varchar column storing numeric values

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, asg.

Asked: September 22, 2016 - 3:04 pm UTC

Last updated: September 27, 2016 - 12:40 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom

I have a table 'LOCS' with 2 columns of varchar datatype (col1 is varchar yet at the moment we have only numeric values stored):

col1 col2
2272 ABC
22722 ABCD
1000 dgdfg
10001 dfm


Now, when I query the table:
select * from locs where contains(LOCS.col1, :searchtext) >0
ORDER BY instr(UPPER(LOCS.col1), :searchtext2);

If i give the searchtext as 1000 the query results as expected:
1000 dgdfg
10001 dfm

But if i give the searchtext as 2272 the query results as otherwise:
22722 ABCD
2272 ABC

Can you please explain?

What should be the correct way of Ordering when I try to find a 'searchtext' and if it is present in both col1 and col2, then 'col1' will take precedence and those rows should come first and within col1 it should be ordered asc.


CREATE TABLE P2PUSER.P2B_LOCATION_SEARCH
( LOC_SEARCH_ID NUMBER(20, 0) NOT NULL PRIMARY KEY
, SALE_UNIT_ID NUMBER(15, 0) NOT NULL
, SALE_UNIT_NAME VARCHAR2(175 BYTE)
, SERIAL_NUMBER VARCHAR2(6 BYTE)
, COMPANY_NAME VARCHAR2(175 BYTE)
, CREATED_ON TIMESTAMP(6)
, UPDATED_ON TIMESTAMP(6)
, STATUS NUMBER(1, 0)
, PROFILE_STATUS NUMBER(1, 0)
, SALE_UNIT_TYPE NUMBER(1, 0)
, GEO_LOCATION "PUBLIC".SDO_GEOMETRY DEFAULT MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)
, LOCATION_UNIQUE_IDENTIFIER VARCHAR2(225 BYTE));

CREATE INDEX P2PUSER.LOCSEARCH_IDX1 ON P2PUSER.P2B_LOCATION_SEARCH (GEO_LOCATION) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('LAYER_GTYPE=POINT') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX2 ON P2PUSER.P2B_LOCATION_SEARCH (SERIAL_NUMBER) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX3 ON P2PUSER.P2B_LOCATION_SEARCH (SALE_UNIT_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER LOCSEARCH_lexer WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX4 ON P2PUSER.P2B_LOCATION_SEARCH (COMPANY_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER LOCSEARCH_lexer WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;

CREATE INDEX P2PUSER.LOC_IDX5 ON P2PUSER.P2B_LOCATION_SEARCH (LOCATION_UPDATION_TIME ASC) ;
CREATE INDEX P2PUSER.LOC_IDX6 ON P2PUSER.P2B_LOCATION_SEARCH (SALE_UNIT_ID ASC) ;
CREATE INDEX P2PUSER.LS5_IDX ON P2PUSER.P2B_LOCATION_SEARCH (STATUS ASC, PROFILE_STATUS ASC) ;
CREATE UNIQUE INDEX P2PUSER.SYS_C0015130 ON P2PUSER.P2B_LOCATION_SEARCH (LOC_SEARCH_ID ASC) ;

SELECT RES.* FROM
(SELECT RES1.*, rownum rnum FROM
(SELECT
/*+ FIRST_ROWS(25) */
LOCS.SALE_UNIT_ID,
LOCS.SERIAL_NUMBER,
LOCS.SALE_UNIT_NAME,
LOCS.COMPANY_NAME,
LOCS.SALE_UNIT_TYPE,
(CASE
WHEN UPPER(LOCS.SERIAL_NUMBER) LIKE :searchtext1 THEN 2
WHEN UPPER(LOCS.SALE_UNIT_NAME) LIKE :searchtext1 AND UPPER(LOCS.SERIAL_NUMBER) NOT LIKE :searchtext1 THEN 3
WHEN UPPER(LOCS.COMPANY_NAME) LIKE :searchtext1 AND UPPER(LOCS.SERIAL_NUMBER) NOT LIKE :searchtext1 AND UPPER(LOCS.SALE_UNIT_NAME) NOT LIKE :searchtext1 THEN 4
ELSE 5
END) RN1
FROM P2PUSER.P2B_LOCATION_SEARCH LOCS
WHERE (contains(LOCS.serial_number, :searchtext)>0
OR contains(LOCS.sale_unit_name, :searchtext) >0
OR contains(LOCS.company_name, :searchtext) >0 )
AND LOCS.SALE_UNIT_TYPE =1
AND LOCS.STATUS = 1
AND LOCS.PROFILE_STATUS IN (1,2)
ORDER BY RN1,
instr(UPPER(LOCS.SERIAL_NUMBER), :searchtext2),
instr(UPPER(LOCS.SALE_UNIT_NAME), :searchtext2),
instr(UPPER(LOCS.COMPANY_NAME), :searchtext2)
) RES1
WHERE Rownum <= :lastIndex
) RES
WHERE RES.Rnum >= :startIndex;
:searchtext is '1000%'
:searchtext1 is %1000%
:searchtext2 is '1000'
THis is the same query which I had discussed in another question with you was consuming 90% CPU on DB server. Now we have attached a SQL profile to resolve the issue.
But there is still problem with the ordering.


and Chris said...

The issue is instr will return the same value for both rows! So the ordering is non-deterministic. You're just lucky you get the correct ordering first time around.

If the columns always contain numbers you can get the "correct" ordering by applying a to_number():

create table t (
  x varchar2(10), y varchar2(10)
);
insert into t values ('2272', 'ABC');
insert into t values ('22722', 'ABC');

create index i on t(x) indextype is ctxsys.context ;

select x, instr(x, '2272') from t
where  contains (x, '2272%', 1) > 0
order  by to_number(x);

X      INSTR(X,'2272')  
2272   1                
22722  1 


But then, if they always contain numbers, why are you storing them as strings?!

If you can have letters in your columns and you still want the numbers sorted numerically, take a look at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522557800346940016

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

asg, September 26, 2016 - 2:02 pm UTC

Thanks Chris!

However, I keep getting 'invalid number' error when I try this out.
Also, at the moment only numbers are stored, in future we may get alphanumberic.

There are 2 scenarios in Order by logic in the query:
1. When the column Serial number contains the searchtext then these rows should take precedence & appear first
2. When Serial number does not contain searchtext and sale unit name contain, these should appear second
3. When serial number and sale unit name do not contain searchtext and only company name contains, these should appear third in the ordering of the rows fetched.

Again within each column the rows should be ordered as per the ascending order of the searchtext ('2272' first and '22722' second) and not ascending order of the numbers (though we have only numbers for now)!

With the discussed solution I am not able to get the desire result for the Serial number, hence I modified as
decode(rn1,5, serial_number,2, locs.serial_number, instr(UPPER(LOCS.SERIAL_NUMBER), :searchtext2)).
But this will not work when serial number starts storing alphanumeric.

Please assist.
Chris Saxon
September 27, 2016 - 12:40 am UTC

There is of course an inherent risk in applying a to_number function around something that may not in fact be a number. You could mitigate against this using a plsql function, eg

create or replace
function my_to_number(p_str varchar2) return number is
begin
  return to_number(p_Str);
exception
  when others then return null;
end;


and then craft your SQL to try to call this as few times as possible to keep any performance overheads down, eg

order by 
case 
  when replace(translate(trim(xxx),'0123456789','0'),'0','') is null 
    then my_to_number(xxx) 
  else ...
end


Cheers,
Connor

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions