Page 1 of 1
Extract Numberics & Chars only from a string
Posted: Tue May 17, 2005 8:10 am
by vinaymanchinila
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,
Posted: Tue May 17, 2005 8:31 am
by talk2shaanc
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.
Posted: Tue May 17, 2005 8:36 am
by vinaymanchinila
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.
Posted: Tue May 17, 2005 10:59 pm
by ray.wurlod
You will need to create a custom routine. There is no intrinsic function for this particular task.
Posted: Wed May 18, 2005 8:17 am
by vinaymanchinila
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"), ".","")
Posted: Wed May 18, 2005 8:49 pm
by ray.wurlod
Only good if there are no "." characters in your data that you want to keep (decimal points?).
Posted: Thu May 19, 2005 5:18 am
by vinaymanchinila
This filed is serial number so fortunately there are no "." in the number.
Posted: Thu May 19, 2005 6:52 am
by roy
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,
Posted: Thu May 19, 2005 8:26 am
by vinaymanchinila
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.