Solved

removing-disabling email properties

Posted on 2013-01-18
13
376 Views
Last Modified: 2013-01-25
Hello

Have a clob field which in some cases an email address is entered.
When this field is shown in reporting tool it has the properties of an email address and if you hover over it and click then yes it goes to email. However, I do not wan that to happen - how can I disable this in my query by maybe using regular expression. Using Oracle 11g

Regards
0
Comment
Question by:PHIL Sawyer
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 200 total points
ID: 38792661
you'll have to disable the capability in the reporting tool

or put a replace around the '@' in the query
select replace(column, '@', '$') column from table;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38792831
What is the reporting tool?
0
 

Author Comment

by:PHIL Sawyer
ID: 38792872
Crystal Reports
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38792901
You might be able to kludge something together to manipulate the data but my guess is you can control this from inside the report itself.

We have some world-class Crystal Report Experts on the site and they should be able to help you.

If this cannot be one in Crystal, maybe we can come up with some data hack that will fool Crystal into not seeing it as a link.
0
 

Author Comment

by:PHIL Sawyer
ID: 38793095
Thanks
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 38793515
I've actually had this same issue when using another product.  I found they were using a simple (VERY SIMPLE) regex to determine what was an email/URL.  I didn't want 'live' links displayed either.

I attempted to fudge my data with a non-printable character to get their regex to no longer recognize data as a valid email address and not alter the data presented to the user.  Unfortunately their regex was to simplistic to fool.  It tagged actual invalid email addresses as an email link.

You can give it a try until a Crystal Expert shows up.

Try replace(data,'@','@' || chr(0)) or replace(data,'@',chr(0) || '@' || chr(0))

I forget the other characters I tried but play with some ofthe lower ascii values to see what might work.

Maybe 14 or 15.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 38795455
The easiest way is to create a formula on the CLOB field that replaces all @s with say an & or $ so you know it was there.

One thing to check is the formatting on the field
Right click it
Click FORMAT FIELD
Click the HYPERLINK tab
Verify it is set to NO HYPERLINK and not EMAIL ADDRESS

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38795458
How are you running the reports?

mlmcc
0
 

Author Comment

by:PHIL Sawyer
ID: 38804969
Hello
Running Crystal Reports locally and from the server.
The field is already set to NO HYPERLINK within the Crystal Report.

For now I have used something like this example to remove email addresses.

select
regexp_replace(' test data at fred.perry@test.com  - more emails possible - joe.perry@hotmail.com - more text',
'([^ ]+@[^ ]+)','') as output
from dual

Ideally - it would be good to replace any email addresses as per the followin example - not sure how to do this using regex_replace e.g.

' test data at <email: fred(dot)perry(at)test(dot)com>  - more emails possible - <email: joe(dot)perry(at)hotmail(dot)com> - more text'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38806012
If you go out and look for a regex for email addresses, you'll find a ton of them.  I'm not sure you'll ever find one that is 100% accurate.

That said, how about you cheat a little.  Replace any '@' that has text around it with '<at>'?

See if this helps:
select regexp_replace(
'test data at not an email: @qwert fred.perry@test.com  - more emails possible - joe.perry@hotmail.com - more text',
'([^\s]+)(@)([^\s]+)',
'\1<at>\3')
from dual;
0
 

Author Comment

by:PHIL Sawyer
ID: 38817903
Thanks everybody - I have enough to mull over
0
 

Author Closing Comment

by:PHIL Sawyer
ID: 38817910
Thanks
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now