Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports XI string function problem

Posted on 2008-06-11
12
Medium Priority
?
5,668 Views
Last Modified: 2013-11-16
I'm trying to do a string manipulation in CR XI R2 and it's not working for me.  It's probably simple and I'm overcomplicating it.

I have a string value (named "itemCode") composed of letters and numbers which I pass into a formula field with the attached code.  I need to conditionally "truncate" or cut a certain number of the last characters off and display them (truncated and full) in my report.  In most of my cases the code works, but with a value of R4.0022, I would like to end up with R4.002 but what I'm getting is R4.00. The line that is causing me problems is:
if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"")

It seems to replace the number 2 wherever it finds it, but I only want it to cut off the final 2.  So I would like to replace the 2 only once, starting at the end.  I know I can tell a "replace" function how many times to replace a string but can I tell it to start looking at the end of the string and work backwards?  Another clever function perhaps?  Any help would be appreciated.
thanks

if {chaProducts.itemCode} like "*.000?" then replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")
    else if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"")
        else {chaProducts.itemCode}

Open in new window

0
Comment
Question by:chamlight
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 21765380
Would be using a different function than replace...

I quickly tested (not exactly the same) and got the results expected... So, change @stringfield for chaProducts.itemCode and fix the cirteria (and remove the "formatn:" etc)

It does assume only 1 decimal point in the string.


if      {@stringfield} like "*.0600" then "format1:" + left({@stringfield},instr({@stringfield},".")+2)
else if {@stringfield} like "*.???0" then "format2:" + left({@stringfield},instr({@stringfield},".")+3)
else if {@stringfield} like "*.???3" then "format3:" + left({@stringfield},instr({@stringfield},".")+4)
else                                      "format4:" + left({@stringfield},instr({@stringfield},".")-1)

Open in new window

0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 600 total points
ID: 21765613
Yes you can specify the start position
From Crystal Help
Replace (inputString, findString, replaceString, startPosition)

Try
if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},right({chaProducts.itemCode},1),"",Length({chaProducts.itemCode})-1)

mlmcc
0
 

Author Comment

by:chamlight
ID: 21769684
Thanks to you both...I'll try your suggestions
c
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 21793323
It looks like if the string ends in ".000?", you want to drop that part, otherwise if it ends in ".????", you want to just drop the final character.  Mid is probably a better function for that kind of thing.  Something like:

if {chaProducts.itemCode} like "*.000?" then
  Mid ({chaProducts.itemCode}, 1, Length ({chaProducts.itemCode}) - 5)
else
  if {chaProducts.itemCode} like "*.????" then
    Mid ({chaProducts.itemCode}, 1, Length ({chaProducts.itemCode}) - 1)
  else
    {chaProducts.itemCode}



 FWIW, while you can't actually make Replace work from the end of the string towards the beginning, you could use StrReverse to reverse the string within the Replace, and then use StrReverse again on the result to put it back in the original order.  Probably not the most efficient approach, but it is an option.

 James
0
 

Author Closing Comment

by:chamlight
ID: 31466402
Thanks very much to all...awarded split points.  James, thanks for the info
0
 
LVL 35

Expert Comment

by:James0628
ID: 21798298
You're welcome.

 FWIW, in this particular case, since you're taking characters from the start of the string up to a certain point, Left () may be a better choice than Mid ().  In theory, it's a little simpler, although I don't know that there's any real difference in what happens behind the scenes.  Whichever function you use, the best (simplest and/or most reliable) way to determine the end point depends on your specific circumstances.

 James
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21798740
@james, yes, I think so as well - esepcially the positioning for the length (end points) - for example "length" function would be interesting if you are dealing with a space filled char column...
0
 
LVL 35

Expert Comment

by:James0628
ID: 21799586
I'm not sure what you mean by "space filled char column", but it you mean a column containing nothing but spaces, that's not going to match your search patterns anyway.

 In general, there may be times when you need to do things like check the length to make sure that it's greater than X before using something like Length () - X in a string function.  With your specific target strings, it's probably not an issue, although there is a potential problem.

 Your first test is for "*.000?".  That would include strings like ".0003", with nothing in front of the period.  A string like that would get an error using the formula I posted, because you'd end up with Mid () trying to take characters from 1 to 0, and CR doesn't like that.  Left ("string", 0) seems to be OK though, so using Left () instead of Mid () should work.

 I'd actually wondered what would happen if you had a string like ".0003", but hadn't actually checked until just now.

 James
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21800036
Wasn't having a shot, just that many a time in the SQL space, there have been problems with length usage when there was an expectation that any trailing spaces where dropped, or non-existant - which is not always the case. Even in varchar columns if explicitly populated with a trailing space will happily store the trailing spaces... A lot of people assume that the string ends with the last significant digit, I do so myself on many an occasion.  

e.g. using SQL Server to exemplify...
declare @str1 varchar(20)
declare @str2 varchar(20)
declare @str3 char(20)
set @str1 = '123.0003    '
set @str2 = '123.0003'
set @str3 = '123.0003'
select datalength(@str1),datalength(@str2),datalength(@str3)
select '['+@str1+']['+@str2+']['+@str3+']'
select len(@str1),len(@str2),len(@str3)

or, in crystal, set up a formula field and try...
Local StringVar addressLine;
Local NumberVar addrLength;
addressLine := "British Columbia V6X 3W2            ";
addrLength := Length (addressLine);
addressLine [addrLength-16+1 to addrLength]

So, really I was agreeing with your observations that you probably need to "know" your data in order to determine which function is going to best suit.

Generally speaking, if it is the first 'n' characters of a column (numeric or character), then the "left" function has served (me at least) particularly well - it is more tolerant of things like a zero length string, and even the type of column.

By the way, the string "*.000?" was used by the author, so assumed that it was part of the specification, and can include ".0003" - so does that mean I posted the only correct entry (just being very cheeky now, and please, please, do not be offended).
0
 

Author Comment

by:chamlight
ID: 21803338
With all of your help, here's what I came up with...it seems to work with my data so far.  

if {chaProducts.itemCode} like "*.000?" then replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")
    else if {chaProducts.itemCode} like "*.????" then replace({chaProducts.itemCode},{chaProducts.itemCode},(left({chaProducts.itemCode},(Length({chaProducts.itemCode})-1))))
        else {chaProducts.itemCode}


And you're right about having to "know" your data.  When asking you guys for help, it's sometimes hard to fully convey the nature of one's own data.  What is obvious to me, is clearly not obvious to you.  So I commend you for trying to "get into my head" to really help me out.  Thanks

0
 
LVL 35

Expert Comment

by:James0628
ID: 21807498
Mark,

 Sorry.  I didn't think you were "having a shot".  I had actually assumed that you were the OP.  Shows how much attention I was paying.  <wry g>  Maybe some of my comments make more sense now.

 Yeah, trailing spaces can be an issue in some cases, but given the OP's target strings, they're not an issue here.  FWIW, if you think they could be an issue in a CR formula, you could always use TrimRight to make sure there aren't any.

 As for you posting the only correct answer, I don't see anything wrong with mlmcc's suggestion.  My original suggestion would work, as long as there was always something in front of the "." (which may or may not be the case).  And, as you noted, yours would not work if there was more than one "." (which may or may not be the case).  So, really, mlmcc's  answer seems the "surest" of the original answers.
 (And, no, I wasn't offended at all.)

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 21807614
chamlight,

 It took me a minute to figure out what you're doing in the latter part of that formula, but it looks like you're replacing the entire string with everything but the last character.  Assuming that I'm interpreting that correctly, you really don't need the Replace ().  Just use the Left () part (what you're using to replace the string).

if {chaProducts.itemCode} like "*.000?" then
  replace({chaProducts.itemCode},right({chaProducts.itemCode},5),"")
else
  if {chaProducts.itemCode} like "*.????" then
    left({chaProducts.itemCode},(Length({chaProducts.itemCode})-1))
  else {chaProducts.itemCode}


 What you have should work.  It's just a little more complicated than necessary.  And if you have a lot of long strings, doing all of those replaces might affect performance, although I suspect that you'd have to have a _lot_ of long strings in order for it to have a noticeable effect.

 James
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question