Apostrophes in Text

[mtownbound]

This may be a question for the SQL Server board, but I'll ask it anyway. We have CR2008 reporting on a SQL Server 2005 database and I'm having a formatting issue with apostrophes and hyphens in SQL Server. They appear as mangled special characters in the db and my reports. Now before everybody screams "Garbage in, garbage out!!", I'm just looking to see if anyone has a quick script to handle something like this.

Thanks!!!!!

 

[fisheromacse]

what do you mean mangled special characters?
are the mangled parts consistent?
is there only 1 occurrence per entry?
if so you could use a formula to display them correctly in crystal, something like:

//{@unmangle}
IF instr({table.field},"#485lkd2")>0
then replace({table.field},"#485lkd2",chr(39))
else {table.field}


replace "#485lkd2" with the consistent text you want replaced
FYI: chr(39) is a single quote (')

if there are multiple instances within a record, you will need to loop through the field to replace all the funkiness.

If you have any control over the data entry, that would be the place to ensure data meets needed standards before saving to the db. Most of the time, in my experience, that control is sadly lacking.

 

[lbass]

Or you could use:

replace(replace({table.field},"-",""),"'","")

-LB

 

[mtownbound]

Sounds good. Thanks for the feedback!! I'll try these and let you know what I get.


Thanks again!!!!!

 

[mtownbound]

Guys, the replace(replace({table.field},"-",""),"'","")
worked like a charm!!!! Thanks a million!! Now I only have two other questions......

1)I have a couple of these (ie hyphens, quotations, astericks, character returns, etc.) so how can I perform multiple replaces in the formula?

2)To get to the root cause, these characters are being stored in SQL Server. Is there a filter or something I can add to SQL Server to allow for adding hyphens, quotations, etc. to a varchar field in the database?


Thanks again!!!

 

[lbass]

1) The suggestion I showed you demonstrates how to do multiple replaces. You just keep nesting the replaces:

replace(replace(replace({table.field},"-",""),"'",""),"*","") //etc.

-LB

 

[mtownbound]

Worked!! Thanks again!!!