DSXchange

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 .....

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".

This modifcation should be eleminate all the special characters.........That how ETL should be..

Please help me in this.....

Thanks ...a lot......

John

Provide the entire list of special characters in the CONVERT function.

CONVERT('list all special characters here','',In.OracleStringColumn)

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.

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.

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

chulett wrote:One technique that gets mentioned here uses a pair of converts, like so:

Code: Select all

Convert(Convert('ABCDEFGHIJKLMNOPQRSTUVWXZY0123456789','',YourField),'',YourField)

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.

。。。。。。。。。。。。。。。。。。。


It's Cool, thanks