Skip to main content

I am trying to return the LEFT 14 characters known as PO_NBR.  The string starts out as numbers, characters and other assorted **** so I am trying to clean this up by using a regular expression.

 

Here is what I have so far: 

 

SELECT REGEXP_REPLACE(UPPER(PO_NBR),'[^0-9A-Z]','') AS PO_NBR

FROM DTF_BUDGET_DETAIL

GROUP BY PO_NBR

 

When I try to do this:

 

SELECT LEFT(REGEXP_REPLACE(UPPER(PO_NBR),'[^0-9A-Z]',''),14) AS PO_NBR

FROM DTF_BUDGET_DETAIL

GROUP BY PO_NBR

 

I get an error "ORA-00904 "LEFT: invalid identifier"

 

Any ideas on how I might do this?

 

Thank you,

 

Scott

6 respuestas
  1. 21 jun 2019, 20:31

    Ken,

     

    It's pretty gnarly for a SQL statement but it works!

     

    SELECT SUBSTR(REGEXP_REPLACE(UPPER(PO_NBR),'[^0-9A-Z]',''),0,14) AS PO_NBR...

     

    Thanks for the help on this!

     

    Scott

0/9000