Special Character should not get loaded into the Staging tab

[John Daniel]

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

[ArndW]

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

[John Daniel]

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

Please help me in this.....

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

John

[kandyshandy]

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

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

[chulett]

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.

[chulett]

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.

[John Daniel]

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

[hailun]

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