• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Replace Problem

I have data in one of the fields I am reporting against as below

Will for John Smith
Will For John Blogg

I want to remove the Will for to just have the name left i.e. John Smith, John Blogg

I am using the formula below

replace(database_field, 'Will for ','')

When I use this I am left with

John Smith and Will For John Blogg, i.e. it still leaves the one with the capital F for For.

How can make this work so I am left with John Smith, John Blogg from the example data
0
halifaxman
Asked:
halifaxman
  • 3
  • 2
  • 2
1 Solution
 
BlueYonderCommented:
It appears the formula is used not used in the group or details sections.  The formula field must be in the group or details section.  If the field is not group or details sections the formula is not run for each record.
0
 
BlueYonderCommented:
If you want to complete the replacement before crystal loads the data, you can use a t-sql statement to the database.  Here are instructions http://hexcentral.com/articles/crystal-sql.htm.
0
 
PortletPaulCommented:
nest the replace function?

replace(replace(database_field, 'Will for ',''),'Will For ','')
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
mlmccCommented:
You have to account for capitalization which Paul's comment does.  Do you potentially have other capitalization of the Will For?

You can also use

Replace({Database Field},"will for","",1,-1,1)

This specifies
Replace(Database Field, Search String, Replacement String, Start Position, Count, Case Sensitivity)

Count -1 means replace all occurrences
Case Sensitivity - 0 is case sensitive, 1 is for case insensitive


mlmcc
0
 
PortletPaulCommented:
@mlmcc - excellent!
Where does one find formal online doco for crystal functions these days?
0
 
mlmccCommented:
I don't know of an online help.  I use the help from Crystal installed on my machine.

mlmcc
0
 
PortletPaulCommented:
:( pity - thanks anyway
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now