Right Function in Oracle SQL Query

metalteck
metalteck used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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.
johnsoneSenior Oracle DBA

Commented:
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)

Author

Commented:
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Senior Oracle DBA
Commented:
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)

Author

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial