Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Stuck - need to remove HTML tags and text either in SQL or using a formula within the report

ratzo02 (Programmer) (OP)
12 Jul 12 13:10
Using BO Crystal Report XI. I have a column of text that includes all the HTML tags. I need to remove those tags from the field. I started by using "replace(replace(replace(replace........" formula, but the tags change with different languages, I had a formula that was extremely long. What I would like to do, either with a formula or in SQL, is create (what should be simple, but I can't come up with it) a formula that remove "<" and all the text between ">".
This formula would have to be able to repeat multiple times.

I found and tried this:
if right({YourField},1) = "]" then
left({YourField},instr({Yourfield},"[")-1)
else
{YourField}


But BO XI doesn't use the "instr" operator.
Any suggestions?
hilfy ( IS/IT--Management)
12 Jul 12 13:31
Instead of instr(), look at the pos() function that works like instr().

If you have access to create functions in the database, this might be more efficient if you create a stored function that will do this for you in the database instead of doing it in Crystal. I've been able to do something similar in Oracle using regular expressions which would potentially eliminate your need to loop through the data. Then, if your report is just using tables, you would create a SQL Expression that calls the function with field that you want to strip. If your report uses a command, you can just call the function in the select statement of your command.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

ratzo02 (Programmer) (OP)
12 Jul 12 15:13
I agree Dell about having the DB do this work. And that's the plan in a few months (moving data to Netezza) but in the short term I stuck trying to make this work.
We are alot closer, at least I can make BO except the new formula, but now it looks for the last > and whipes out everything if the last character is > (there is a character limit on this field).
Here is the Formula I've started with, and have been tweaking, but so far its all (everything removed) or none (nothing removed)*except*:
=If Right([Case Text];1) = ">" Then
Left([Case Text];Pos([Case Text];"<")-1)
Else [Case Text]
*except*:
=If Right([Case Text];1) = ">" Then
Left([Case Text];Pos([Case Text];"<")+1)
Else [Case Text]
This leaves the first < and character of the entire field as expected. I need to come up with a way for it to see the first "<" then go the the very next ">" and delete everything in between. IF it doesn't have a "<" infront of it, skip over it.
Any thoughts?

Thanks awesome start Dell
-Kurt
hilfy ( IS/IT--Management)
13 Jul 12 9:08
You could try something like this:

CODE

Numbervar nStart := 0;
Numbervar nEnd := 0;
StringVar sProcess := {Your Field};
StringVar sResult := '';

nStart := pos(sProcess, '>');
While nStart > 0 do
(
  nEnd := pos(sProcess, '<');
  sResult := sResult + left(sProcess, nStart - 1) + ' ';
  sProcess := mid(sProcess, nEnd + 1);
  nStart := pos(sProcess, '>');
)
RTrim(sResult) 

This should walk through the whole string and remove everything that starts with "<" and ends with ">".

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close