How can I break out two different values in an Oracle string field (20) and trim off the leading zeros as well as capture specific values.

How can I break out two different values in an Oracle string field (20) and trim off the leading zeros as well as capture specific values.

In the image here.
String field I need help with.I have a string field (20) with values that have a leading set of characters like "CO" , NPC" and or "QU" followed by a dash " - " or two " -- " then a value like 00003.  I need to :
1. consistently pull the value called CO, NPC, QU or whatever is before the dash " - " or two " -- "
2. retrieve the numeric set no matter how long or short the value after the dash " - "  so that if the value is "00003" the value should display as just "3"

What I think I need some combination of the following Crystal Reports Functions:
If Then Else or Select Case
Instr (???) and or InstrRev (???)
Maybe the Trim function and or ToNumber?

I am not sure what is the best way to do this and appreciate any result you can get me. Thank advance.
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
what's your oracle version ?
some versions support regexp ...

my regexp is a little rusty, not finding an optional - doesn't seem to work correctly in my regexp

with d as (
  select 'CO-007' x from dual
  union all 
  select 'NPC--A' from dual)
select x, regexp_substr(x, '[^-]*') first_part, ltrim(regexp_substr(x, '([^-]*)', 1, 3)||regexp_substr(x, '([^-]*)', 1, 4), '0') second_part
from d;

CO-007	CO	7

Open in new window

you'll have to adjust the query to give you the 2 parts
Alex [***Alex140181***]Software DeveloperCommented:
If you can't use REGEXP_xxx functions, this one might help:
select str,
       substr(str, 1, instr(str, '-') - 1) first_part,
         when trim(translate(reverse(substr(reverse(str), 1, instr(reverse(str), '-') - 1)), '0123456789', ' ')) is null then
          to_number(reverse(substr(reverse(str), 1, instr(reverse(str), '-') - 1)))
       end last_part
  from (select 'CO-007' str
          from dual
        select 'NPC--A' str
          from dual
        select 'QU-00145' str
          from dual
        select 'CO--00023' str
          from dual);

Open in new window

Alex [***Alex140181***]Software DeveloperCommented:
Even if you're able to use REGEXP_xxx functions, you will encounter problems dealing with the last part of the string ;-)
My regex would be:

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
What do you want if the part after the - is non-numeric?

Alexander's example will return NULL if the part after the - is non-numeric.  If you want the value, then this should do it, and it doesn't use regular expressions (which can be expensive).

SELECT str, 
       Substr(str, 1, Instr(str, '-') - 1)              first_part, 
       Ltrim(Substr(str, Instr(str, '-', -1) + 1), '0') second_part 
FROM   (SELECT 'CO-007' str 
        FROM   dual 
        SELECT 'NPC--A' str 
        FROM   dual 
        SELECT 'QU-00145' str 
        FROM   dual 
        SELECT 'CO--00023' str 
        FROM   dual); 

Open in new window

Alex [***Alex140181***]Software DeveloperCommented:
and it doesn't use regular expressions (which can be expensive)
I disagree! Most of the time, using built-ins will outperform regular expressions, especially when dealing with large amounts of data (and its processing). As usual: it depends ;-)
johnsoneSenior Oracle DBACommented:
Most of the time, using built-ins will outperform regular expressions
Isn't that what I said?  Regular expressions can be expensive.
Alex [***Alex140181***]Software DeveloperCommented:
Ok, then. Sorry, but the context was quite misleading ;-)
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
The version of Oracle is 11_2
What I need to do if the value after the " - " is non-numeric. What I will do is I will just search for the values NPC and QU etc. to do something different with them like omit them.

I am not a programmer but have worked with training and consulting with Crystal Reports SAP and Business Objects for going on 20 years. I have always had fairly easy functions to deal with. Since I can follow some of the content you mentioned above and understand what a Select Case is but not things like "dual" and UNION. The only UNION I know relates to table relationships and linking them. SO if you were me, what would you do, where would you go to learn as much as I can about how functions work. Like SQL I know there is a commonality of code to the the different versions of SQL Oracles version Microsofts and the countless nuances of the other brands. My point is if functions were designed in a similar fashion to help standardize the code who what where would be the best place for me to start building my personal knowledge base. The one I my head....!

Your thoughts would be appreciated and thank you for the help. My mentor and friend is mlmcc. One of the best human beings on this earth as far as I am concerned. I would like to have more skills like his. If you have any suggestions on where to start I am all ears. Learning this stuff. It is like where in the world do you start?????
awking00Information Technology SpecialistCommented:
Without regular expressions -
select substr(col1,1,instr(col1,'-') - 1) part1,
ltrim(substr(col1,instr(col1,'-',-1) + 1),'0') part2
from yourtbl;
johnsoneSenior Oracle DBACommented:
What you need to concentrate on are these 2 lines:
       Substr(str, 1, Instr(str, '-') - 1)              first_part, 
       Ltrim(Substr(str, Instr(str, '-', -1) + 1), '0') second_part 

Open in new window

Those 2 lines are what parses out the 2 fields.  You would need to replace STR with the name of the column in your table.

UNION and DUAL are being used in the samples to generate test data using what you provided so we can give you a working query.
awking00Information Technology SpecialistCommented:
@johnsone, isn't that exactly what I posted?
johnsoneSenior Oracle DBACommented:
Well, I posted it first here.  I just copied it right out of my post.
awking00Information Technology SpecialistCommented:
I do apologize, I missed seeing  that post before. At least you know you have my vote for the best answer :-)
You can use the SPLIT function to split it apart

Split({YourField}, '-')

To strip the  leading 0's you can use the Val function then convert back to a string if you want.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
There is no split function in Oracle (I'm sometimes sad to say).
He is looking for a formula in Crystal Reports

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Oh gentlemen I am so when did I post this 4 AM. I thought I had posted I was working on an Oracle Database with Crystal Reports. Uuuuuugggggghhhhhh!. That is why I was confused and I do still need learn some of the tricks of the trade. I bought a couple of classes on UDEMY and will be taking them and trying to understand the code better. It is past time that I did. I will credit points by number of posts, the best answers and the effort which I know was phenomenal. Working on 2 hours sleep trying to get some work completed.  Thank you as always.
Alex [***Alex140181***]Software DeveloperCommented:
I will credit points by number of posts
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Yes sir the relevant ones. If ...if ....if there were 10 post that were useful and helpful out of 18 and 1000 points allotted but 8 that were kind of irrelevant. (Not in this case. Only 7 relevant and 11 irrelevant just back and forth's) Then wouldn't it make sense to give 100 per post. Or maybe a bit more on the best and a little less on the one that does not really contribute as much.

I have never in my life ever been thought of as unfair to my knowledge. you will get your points sir. I promise.
johnsoneSenior Oracle DBACommented:
Just to add....

I know very little about Crystal Reports.  But, using the SPLIT function if there is a -- (two dashes instead of one), not sure if you will get the what you are looking for.  Also, adding the VAL function would likely cause an issues for non-numeric data (there is non-numerics in your sample).

If you use the database solutions that were given, those should cover the potential issues.
VAL will convert the leading numeric digits into a number.

1234ABC will convert to 1234
1234A12 will convert to 1234
A1234 will convert to 0

SPLIT creates an array of strings.
SPLIT('CO-00123', '-') produces a 2 element array with values 'CO' and '000123'
SPLIT('CO--00123', '-') produces a 3 element array with values 'CO', '', and '000123'

You would have to use the VAL like this
VAL(Split({YourField}, '-')[UBound(Split({YourField}, '-')])

johnsoneSenior Oracle DBACommented:
So, NPC--A would convert to a 3 element array, with NPC as the first element, an empty string as the second element and A as the third element, but VAL will convert the A to 0.  Is that the desired result?  Seems like you are losing data.
slightwv (䄆 Netminder) Commented:
>>Yes sir the relevant ones

You shouldn't reward participation.  Give points to the post or posts that either solve your problem or directly contribute to helping you solve it.

I can probably post several more examples that might also provide the same results but likely won't be efficient or "best".
Alex [***Alex140181***]Software DeveloperCommented:
you will get your points sir
That was not my intention (trying to claim/gain points)! As slightwv posted above, I just wanted to point out, that you should NOT just distribute points upon participation ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.