cancel
Showing results for 
Search instead for 
Did you mean: 

Need to replace short dash between numbers without replacing the short dash between words

former_member1110314
Participant
0 Kudos

Hi there

Here is an example of a line of text: Bio-synthetic valve, c. 1995-2001

In the above statement, I want to replace the short dash that appears between the years, but not the one that appears in the conjoined word. Is there a way I can search out 2 numeric characters followed by short dash followed by 2 numeric characters and replace the short dash with a longer em dash?

Desired end result: Bio-synthetic valve, c. 1995—2001

Any help would be greatly appreciated!

cheers

Karen

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Or you could just test for numbers

If isnumeric(replace(yourfield,"-", "") then replace(yourfield,"-", "chr(336)") else yourfield

Ian

former_member1110314
Participant
0 Kudos

Hi there Ian

Thanks for your response!

It seems for your answer to work that the field needs to be entirely numeric? The field is a text field, with some numerical strings that we want separated with the em dash rather than a normal short dash, as per example above. When I use this formula, nothing happens, presumably because the whole field is not numeric?

cheers

KB

Former Member
0 Kudos

Try adding trim to remove Trailing spaces or use replace to remove spaces

If isnumeric(replace(replace(yourfield, " ",""),"-", "") then replace(yourfield,"-", "chr(336)") else yourfield

Ian
Former Member
0 Kudos

If that does not work can we see some real data, ie samples of contents of field.

you gave the example 1995-2001

If numbers only appear like that then formula should work, if format is different then you will need to modify formula accordingly

Ian

former_member1110314
Participant
0 Kudos

Hi there Ian

An example of the entered field content is:

Bio-synthetic valve, c. 1995-2001

I need the dash between the numerical values (ie. between 1995 and 2001) to be an em dash, so I figured the simplest way to do that would be to ask Crystal to turn any dash that comes after two numerals and before 2 numerals (ie. is in between them), to turn that dash into an em dash. The reason why I nominate 2 digits is that there may be cases where the user enters only 2 digits in the date range, so all possibilities are covered if I just search on 2 rather than 4.

Hope that makes sense!

cheers

Karen

DellSC
Active Contributor
0 Kudos

Yes, there is a way to do this using a couple of variables and a loop in a formula. It looks something like

StringVar result := ' ';
Local NumberVar strLen := Length({MyTable.MyField});
Local NumberVar i := 1;
While i < strLen do
(
  if {MyTable.MyField}[i] <> '-' then
    result := result + {MyTable.MyField}[i]
  else 
    if (i < strLen) and IsNumeric({MyTable.MyField}[i-1]) and 
      IsNumeric ({MyTable.MyField}[i + 1]) then
      result := result + chr(336)
    else
      result := result + {MyTable.MyField}[i]
);
result

Note that "chr(336)" is an emdash in the Arial font. You'll want to check whatever font you're using to determine what character you need to use.

-Dell

former_member1110314
Participant
0 Kudos

Hi there Dell

Many thanks for your response! I've put this formula in and it is accepted. However when I try to preview the report, I get an error that states "A string can be at most 65534 characters long.".

Do you know what is causing this?

cheers

Karen

DellSC
Active Contributor
0 Kudos

What type of field is this in the database and which version of Crystal are you using?

-Dell

former_member1110314
Participant
0 Kudos

The field is a formula, based on conditions in other fields. Is there some way I have to express the field so that this formula can work?

-Karen

former_member1110314
Participant
0 Kudos

Oops and version is XI .

DellSC
Active Contributor
0 Kudos

To do the comparison correctly, both the field/formula and the parameter need to be converted to dates or they need to be strings in the format yyyyMMdd. Either way will work.

-Dell

former_member1110314
Participant
0 Kudos

Hi there Dell

I'm not sure I understand your comment regarding date format. The formula field is not a date field, nor is it made up from an original date field (it is just a conditional formula that spits out text depending on the presence of certain data), so I'm not sure how I can tell Crystal that it is a date, when there are other bits of text in there that do not conform to a date format?

KB

former_member1110314
Participant
0 Kudos

Hi again Dell

Essentially, my vision is a formula that will say:

"If there are two numerals followed by a short dash followed by 2 numerals within {myfield} [string1]), then within [string1], replace the short dash with an em dash".

The text entries are not necessarily uniform with regard to date format, so I don't believe there is any way I can apply any date format rules.

Hope that makes sense!

cheers

Karen

DellSC
Active Contributor
0 Kudos

Sorry about that - I was thinking of another question I've been working with.... 😛

-Dell

DellSC
Active Contributor
0 Kudos

Please post the formula that you're using. Thanks!

-Dell

former_member1110314
Participant
0 Kudos

Hi Dell

The formula field ("Label") to which I want to apply the new formula to check for dashes is set up like this:

{Label}

IF "#Label#" in({Object_csv.ObjObjectNotes}) then ExtractString({Object_csv.ObjObjectNotes}, "#Label#", "#End#")

So, quite often the people populating the csv.ObjObjectNotes fields between #Label# and #End# will just use short dashes, but the graphic designer wants long dashes between the dates, so this is what has brought me here to this forum.

An example of the entry in the csv.ObjObjectNotes field:

#Section#Medical implants#Label#Bio-synthetic valve, c. 1995-2001#End#

and desired result in the field with adjusted long dash between numerals...

Desired end result: Bio-synthetic valve, c. 1995—2001

With your guidance, I have set up a formula called "LabelDash" which looks like this (well not quite like this, as I have the same line feed structure as you gave me but this interface strips them out):

{LabelDash}

StringVar result := ' '; Local NumberVar strLen := Length({@Label}); Local NumberVar i := 1; While i < strLen do ( if {@Label}[i] <> '-' then result := result + {@Label}[i] else if (i < strLen) and IsNumeric({@Label}[i-1]) and IsNumeric ({@Label}[i + 1]) then result := result + chr(336) else result := result + {@Label}[i] ); result

...but when I put {LabelDash} into the report, i get the error message "A string field can be at most 65534 characters long."

cheers

Karen

DellSC
Active Contributor
0 Kudos

Ok, I think I know what's going on. The ObjObjectNotes field is a blob, clob, or varchar(max) field, which can potentially be longer that Crystal can handle. So, we need to limit the length of the string that we can look at. You could try something like this:

StringVar result := ' '; 
StringVar smallString := left({@Label}, 65500);
Local NumberVar strLen := Length(smallString);
Local NumberVar i := 1;
While i < strLen do ( if smallString[i] <> '-' then result := result + smallString[i] else if (i < strLen) and IsNumeric(smallString[i-1]) and IsNumeric(smallString[i + 1]) then result := result + chr(336) else result := result + smallString[i] ); result


-Dell

former_member1110314
Participant
0 Kudos

Hi there Dell

Alas, the same error message about string length is still popping up.

Any further ideas?

cheers

Karen

DellSC
Active Contributor
0 Kudos

The only other thought I have would be to create a stored function in the database that would handle this and then, if you're linking tables in the report, create a SQL Expression that calls the function or, if you've used a command in the report, include the function call in the command's Select.

-Dell

former_member1110314
Participant
0 Kudos

Hi there Dell,

That is beyond my knowledge. The database is a proprietary museum collection database, so I can't change the way it is configured, I can only set up a report to extract the information I need.

Thank you so much for all your assistance!

-Karen

Former Member
0 Kudos

If you can't build an SP on database convert report to a command and restrict field size in SQL.

Ian