We help IT Professionals succeed at work.

how to trim value in Long datatype

chalie001
chalie001 asked
on
146 Views
Last Modified: 2017-01-09
hi i have the following query

select -- substr(msg_name,1,8),
         fld_seq_no,
          substr(fld_name,1,40) fld_name, fld_type, fld_length,
          fld_precision
     from message_dict
    where msg_name like ('&msg')
    order by fld_seq_nO

Open in new window

;
 which retun following values
  FLD_SEQ_NO FLD_NAME   FLD_TYPE      FLD_LENGTH  FLD_PRECISION
--------------------------------------- ---------------------------------------- -------- --------------------------------------- ---------------------------------------
  10 IDMSTYPE_100                  CH                   1
  20 SASTIME_100                     DF                14
                                     
               
                        
                  i have another query which got this
SQL> SELECT MSG_TEXT FROM msg_in WHERE stream = 'TESTBASIC'
 
MSG_TEXT
--------------------------------------------------------------------------------
U201605170000001122334455667788990065465465465033024tONYTEST

i what the value to display as

I what to trim the msg_text based on the FLD_LENGTH AND POPULATE in extra column

FLD_SEQ_NO FLD_NAME         FLD_TYPE    FLD_LENGTH        FLD_PRECISION          Value
--------------------------------------- ---------------------------------------- -------- --------------------------------------- -----------------------------------------------------------
   10                IDMSTYPE_100      CH                1                                                                 U
   20     SASTIME_100                    DF               1                                                                 20160517000000
                                     
                                                      

desc msg_in
Name          Type        Nullable Default Comments
------------- ----------- -------- ------- --------                            
MSG_TEXT      LONG
Comment
Watch Question

johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Your biggest hurdle is going to be the LONG datatype.  Why are you using that?  That was deprecated a long time ago.  You really should be using a CLOB.

If you cannot change the data type, then I believe your best option is going to be to temporarily convert it to a CLOB.  The unfortunate part there is that it would involved additional steps.  The easiest way I know if to do it is using the TO_LOB function, however that can only be used in an insert statement.  I would convert the LONG to a CLOB using a global temporary table, then you can use the DBMS_LOB.SUBSTR function to do the substrings that you need.

Author

Commented:
How can I do that
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.