Server function to extract a particular string

Aquilis · Post by **Aquilis** » Wed Jun 18, 2008 5:02 am

Hi all,
I have routine which connects to database and gets the result. My output from the routine looks like:

Ans = 
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 18 06:43:41 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 
EMAILID
--------------------------------------------------------------------------------
Aquilis@yahoo.com

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
.

But now i want have only email address like :
Aquilis@yahoo.com

So what would be the Server functions so that i can achieve the required output : "Aquilis@yahoo.com"
I have tried with following function:

Ans=trimF(trim(Field(Field(Output,'>',2),'',1),'-','A'))
but i am able achieve upto :

Code: Select all

Ans = 
EMAILID

Aquilis@yahoo.com

SQL

------

I appreciate if you guyz suggest me any better approach Or suggest me how to remove Line Feed characters "\n".

chulett · Post by **chulett** » Wed Jun 18, 2008 5:26 am

How about:

Code: Select all

Ans=Output<14>

Assuming I counted correctly.

Aquilis · Post by **Aquilis** » Wed Jun 18, 2008 5:55 am

Could you elaborate more...
Actaully there are some newline cahracters involved in it after firing my function:
Ans=trimF(trim(Field(Field(Output,'>',2),'',1),'-','A')) ..

i want the result which exists in 4th line.
4th line has got my email id which i need to extract.

Thanks..

ArndW · Post by **ArndW** » Wed Jun 18, 2008 5:58 am

I would do this in 2 steps.

Code: Select all

MailAddress = ''
LOCATE '----------' IN Ans SETTING ColumnHeaderPos ELSE ColumnHeaderPos = 0
IF ColumnHeaderPos THEN MailAddress = Ans<ColumnHeaderPos+1>

This would cater for changes in the line position in the future.

chulett · Post by **chulett** » Wed Jun 18, 2008 6:07 am

Actually, I meant without 'firing your function' - i.e. on the original output. Why not just suppress all of that extra who-ha so all you get is the answer? Your DBA can educate you on the how of that.

Aquilis · Post by **Aquilis** » Wed Jun 18, 2008 6:35 am

I edited my existing routine and without using any datastage functions Some how i managed to get output as shown below :

Code: Select all

Ans=
Aquilis@yahoo.com

now all i need is only emailid part like:

Code: Select all

Ans=Aquilis@yahoo.com

chulett · Post by **chulett** » Wed Jun 18, 2008 6:49 am

Somehow? :D

Remove anything from the string you don't want with either Convert or EReplace. Both have been discussed ad nausem here.

ArndW · Post by **ArndW** » Wed Jun 18, 2008 6:50 am

The "somehow" part makes me nervous... but it seems that you still have a line break, so a derivation of "In.String<2>" should do the trick.

Aquilis · Post by **Aquilis** » Wed Jun 18, 2008 7:21 am

Andrew/Chulet,

with your help ,we are able to do it.
your "In.string<2>" done the magic.
If i would have able to get your "output<14>", that would have only done this magic early.

anyway Thanks in tonnes.