Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Right Function in Oracle SQL Query

Posted on 2016-09-02
6
Medium Priority
?
421 Views
Last Modified: 2016-09-02
I have a field in my database and the only thing I need to capture is everything except the last 3 characters of the field.
Ex.
FieldName                           Expected Result
EDITradepartners850        EDITradepartners
MMCCEDI850                      MMCCEDI

I tried using the left function, but since the names vary in length, I don't thing that's a good way to do it.

Any help would be appreciated.
0
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41782281
I would think it should be:

substr(fieldname, 1, length(fieldname)-3)

As far as I know, Oracle doesn't have a LEFT or a RIGHT function.  However, the function of those can be done with SUBSTR.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41782286
If you are trying to get the Oracle equivalent of the LEFT and RIGHT function, then these would be equivalent:

LEFT(FieldName, 10) = SUBSTR(FieldName, 1, 10)
RIGHT(FieldName, 3) = SUBSTR(FieldName, -3)
0
 

Author Comment

by:metalteck
ID: 41782295
Don't think that's working.

This is the code I'm using:
select edt.edi_nbr,
substr(edt.edi_nbr,1,length(edt.edi_nbr)-3) as TPID
FROM PROD.EDTRDPTNRS edt

The results:
3M850                           3M850
AAF850                          AAF850
ABIOM850                    ABIOM850
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41782301
Works for me:
WITH edtrdptnrs 
     AS (SELECT '3M850' edi_nbr 
         FROM   dual 
         UNION ALL 
         SELECT 'AAF850' edi_nbr 
         FROM   dual 
         UNION ALL 
         SELECT 'ABIOM850' edi_nbr 
         FROM   dual) 
SELECT edt.edi_nbr, 
       Substr(edt.edi_nbr, 1, Length(edt.edi_nbr) - 3) AS TPID 
FROM   prod.edtrdptnrs edt; 

Open in new window

Produces this result:
EDI_NBR  TPID
-------- --------------------------------
3M850    3M
AAF850   AAF
ABIOM850 ABIOM

Open in new window

If it isn't working on your end, what is the dataype of EDI_NBR?  Is it CHAR or VARCHAR?  If CHAR, then it is working and just removing the last 3 blank spaces.  You would have to change it to:

substr(edt.edi_nbr,1,length(rtrim(edt.edi_nbr))-3)
0
 

Author Comment

by:metalteck
ID: 41782316
Looks like I may have whitespace at the end.
I used both the trim and rtrim function, but here are the results I get.

EDI_NBR      TPID      TPID_2
3M850                3M850      M85
AAF850               AAF850      F85
ABIOM850             ABIOM850      M85
ACCLARENT850         ACCLARENT850      T85
ACRACUT850           ACRACUT850      T85
ACUDERM850           ACUDERM850      M85
0
 

Author Closing Comment

by:metalteck
ID: 41782319
Didn't see your comment on using the rtrim in the length.
I did that and it work.
Thanks for all the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question