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