?
Solved

removing-disabling email properties

Posted on 2013-01-18
13
Medium Priority
?
399 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
12 Comments
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 800 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 78

Expert Comment

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

Author Comment

by:PHIL Sawyer
ID: 38792872
Crystal Reports
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 78

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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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 400 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 78

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 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