This is a little hard to follow.
It is very easy to convert unicode to ascii - 256 and lose the extended unicode characters. If that is useful to you, I can show you how.
It gets more complicated in a hurry if you need to keep it as unicode.
Remove special characters (¿) from the varchar data type
Moderators: chulett, rschirm, roy
To me, this is a classic case of using BYTE syntax for a column like this when you really shouldn't. IMHO it would be worth asking your architect / DBA if it can be modified to use CHAR syntax instead so it stores 4000 characters rather than 4000 bytes in the field:
From
BIG_FIELD VARCHAR2(4000 BYTE)
To
BIG_FIELD VARCHARS(4000 CHAR)
From
BIG_FIELD VARCHAR2(4000 BYTE)
To
BIG_FIELD VARCHARS(4000 CHAR)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Ascii byte streams are legal unicode. Depending on what exactly you DO, you may not have to change any metadata. It just depends on your approach.rajudx wrote:if we will convert from Unicode to ascii - 256 that require to change in datastage job metadata on output stage.
Are there a few specific values in your data that are causing the trouble? In that case, a couple of nested ereplace might be all you need. If it is more than a few values, a routine can do the same more efficiently. Or you can do the reverse, and only keep certain characters.
The real question is data driven. Are these characters meaningful to your data? What do you expect to go into your target, based off the your input?
The algorithm you cook up depends on your specific data and needs.
Yeah, it continues to confound me how many people encounter "special" or even better the so-called "junk" characters and simply want to nuke them. Far better IMHO to recognize what they are as your client's data and accommodate them properly, and which ways to do exactly that have been discussed here. Ah well.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers