Avatar of chalie001
chalie001
 asked on

how to trim value in Long datatype

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
DatabasesOracle Database

Avatar of undefined
Last Comment
chalie001

8/22/2022 - Mon
johnsone

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.
chalie001

ASKER
How can I do that
SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes