ETL designed to move the source data to staging table,
Here source is Oracle table and Staging is also a Oracle table In the source data one of the column's data is comming like this.....
APRN
MS, D.Ph
NP#12391
MS, RD, LD, CDE
AO3143
phd
g
\\\\\\\\\\
RN/CDE
nhnnn b v
CPHT
RN,BSN,CDE
`
FACP,FRCP-C,FACE
Ph.D.
MD/CDE
MD, DrPH
These special character should not get loaded into the Staging table.....
How can I modify the ETL not to load these special characters into the staging table.
Please advise me in this..... Thanks .....
Special Character should not get loaded into the Staging tab
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 42
- Joined: Mon Oct 15, 2007 10:35 pm
- Location: Charlotte
- Contact:
The CONVERT() function can be used to remove characters from a string. It is not clear which characters you consider to be "special", but the function CONVERT('\#`','',In.OracleStringColumn) would strip out all "\", "`" and "#" characters from the "OracleStringColumn".
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 42
- Joined: Mon Oct 15, 2007 10:35 pm
- Location: Charlotte
- Contact:
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
There's nothing "special" about any of those characters, that is why people are asking you to clarify - be specific - about what you mean by special, what exact list of characters you want to remove.
Regardless, Convert() is the resolution, not any kind of a 'workaround'. If you still need help with this, let us know.
Regardless, Convert() is the resolution, not any kind of a 'workaround'. If you still need help with this, let us know.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
One technique that gets mentioned here uses a pair of converts, like so:
This would remove everything except numbers and upper-case letters. If you have the need, you could add the lower-case alphabet to the mix as well.
Code: Select all
Convert(Convert('ABCDEFGHIJKLMNOPQRSTUVWXZY0123456789','',YourField),'',YourField)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 42
- Joined: Mon Oct 15, 2007 10:35 pm
- Location: Charlotte
- Contact:
Special Character should not get loaded into the Staging tab
Thanks for your kind reply ...............the above given example will workout as I hope .......let me try this....
I mean Only Alpha and Numerics should get loaded in the staging table...........
Thanks,
John
I mean Only Alpha and Numerics should get loaded in the staging table...........
Thanks,
John
。。。。。。。。。。。。。。。。。。。chulett wrote:One technique that gets mentioned here uses a pair of converts, like so:
This would remove everything except numbers and upper-case letters. If you have the need, you could add the lower-case alphabet to the mix as well.Code: Select all
Convert(Convert('ABCDEFGHIJKLMNOPQRSTUVWXZY0123456789','',YourField),'',YourField)
It's Cool, thanks
Let's Progress Together....