Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    27

    Unanswered: Convert CLOB to varchar2 for ORDER BY?

    I am developing an app based on SQL Server, but I want to make it as easy as possible to port it to Oracle sometime later.

    I have some fields that need to hold >4000 characters, so I'm storing their data in the SQL Server version of a CLOB field. However, I need to ORDER BY those fields.

    In SQL Server, I can do this: "ORDER BY CAST(field AS varchar(100))", and it works fine.

    Will that work in Oracle, too?

    Josh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes:
    Code:
    SQL> create table t (id int primary key, text clob);
    
    Table created.
    
    SQL> insert into t values (1, rpad('x',4000,'x'));
    
    1 row created.
    
    SQL> update t set text=text||text
      2  /
    
    1 row updated.
    
    SQL> select length(text) from t;
    
    LENGTH(TEXT)
    ------------
            8000
    
    SQL> select id from t
      2  order by cast(text as varchar2(100))
      3  /
    
            ID
    ----------
             1

  3. #3
    Join Date
    Jan 2005
    Posts
    27
    Great, thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •