Extract Numberics & Chars only from a string

vinaymanchinila · Post by **vinaymanchinila** » Tue May 17, 2005 8:10 am

Hi,
Is there a function or way to extract only numbers and characters from a string. I need to do this so to avoid special characters and line termination characters.
Thanks,

talk2shaanc · Post by **talk2shaanc** » Tue May 17, 2005 8:31 am

to get only numeric value from a string : oconv(string,"MCN")
to get only alphabetic value: oconv(string,"MCA")
to get numeric+special character value from a string : oconv(string,"MC/A")
to get only alphabetic+special character value: oconv(string,"MC/N")

You look for "Masked Character Conversions" in the Datastage help documents, you will get to know more.

vinaymanchinila · Post by **vinaymanchinila** » Tue May 17, 2005 8:36 am

Thanks,
Will try this, I havea field value like MCE90908 which is extracted from Oracle8i and has a line termination at the end which is invisible, so wanted to extract only numerics and characters . What would be the combination to extract numbers AND characters only.
Appreciate your help.

ray.wurlod · Post by **ray.wurlod** » Tue May 17, 2005 10:59 pm

You will need to create a custom routine. There is no intrinsic function for this particular task.

vinaymanchinila · Post by **vinaymanchinila** » Wed May 18, 2005 8:17 am

Hi,
Thanks for the Ocnv idea, have got my issue resolved by first capturing and converting any special characters to "." and then replaceing the "." to ""!

TRIM(Oconv(ToXfm.LONG_SERIAL_NO,"MCP"), ".","")

ray.wurlod · Post by **ray.wurlod** » Wed May 18, 2005 8:49 pm

Only good if there are no "." characters in your data that you want to keep (decimal points?).

vinaymanchinila · Post by **vinaymanchinila** » Thu May 19, 2005 5:18 am

This filed is serial number so fortunately there are no "." in the number.

roy · Post by **roy** » Thu May 19, 2005 6:52 am

Hi,
For some strange reason I can't help feeling like the problem might be something like reading a windows source file that still has CRLF line termination instead of LF only as in unix.
if that is the case you can use the CRLF style on the sequential file or make sure when it is transfered to you it converts the Line termination style between different platforms.

if you do this then you won't have a problem.

so I ask does my 6th scense work?

IHTH,

vinaymanchinila · Post by **vinaymanchinila** » Thu May 19, 2005 8:26 am

It sure is scary 6th sense! but I donot have hte flat file access as SAP BW Plug in creates the flat file internally. Other way is first create a flat file and do as you say and again load it to BW plug in..Hmm yes that sounds do able.
Will let you know Thanks for the idea.