Skip to Content
0

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

Apr 23 at 11:34 PM

127

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ian Waterman Apr 24 at 07:27 AM
0

Or you could just test for numbers

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

Ian

Show 4 Share
10 |10000 characters needed characters left characters exceeded

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

0

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
0

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

0

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

0
Dell Stinnett-Christy Apr 24 at 12:19 AM
0

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

Show 15 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

-Dell

0

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

0

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

0

Oops and version is XI .

0

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

0

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

-Dell

0

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

0

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

-Dell

0
Dell Stinnett-Christy

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

0

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

0
Dell Stinnett-Christy

Hi there Dell

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

Any further ideas?

cheers

Karen

0

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

0
Dell Stinnett-Christy

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

0

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

Ian

0