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.
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 " -- "
and
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 you.....in advance.
In the image here.
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 " -- "
and
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 you.....in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
Alex [***Alex140181***]
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 ;-)
Most of the time, using built-ins will outperform regular expressionsIsn't that what I said? Regular expressions can be expensive.
Ok, then. Sorry, but the context was quite misleading ;-)
ASKER
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?????
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?????
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@johnsone, isn't that exactly what I posted?
I do apologize, I missed seeing that post before. At least you know you have my vote for the best answer :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is no split function in Oracle (I'm sometimes sad to say).
He is looking for a formula in Crystal Reports
mlmcc
mlmcc
ASKER
Oh gentlemen I am so sorry.....now 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.
I will credit points by number of postsSeriously?!
ASKER
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.
I have never in my life ever been thought of as unfair to my knowledge. you will get your points sir. I promise.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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".
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".
you will get your points sirThat 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 ;-)