Solved

removing-disabling email properties

Posted on 2013-01-18
13
381 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
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…

860 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