Solved

removing-disabling email properties

Posted on 2013-01-18
13
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

Expert Comment

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

Author Comment

by:PHIL Sawyer
ID: 38792872
Crystal Reports
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 77

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 77

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
 
LVL 101

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 101

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 77

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

696 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