Hello all, How can I delete certain character from my data export? I am exporting data from a table whose fields are all strings and they may contain '|' in the data. I would like to remove this '|' from the fields while importing to my Unix machine because '|' is also my field delimiter, it is messing up my Unix scripts where I am checking for number of fields and other stuff.
Not easy to do with the TD string functions (which in my opinion are very limited). You can probably code a UDF to replace a string (you may find one already done if you search around).
Why not choose a different delimiter?
If that'a a problem due to the scripts in Unix, you can always export with a different delimiter, then use SED on the file to strip the | from the file and then use SED again to switch the delimiters you used to |.
A bit conoluted, but if you pick the right delimiter on the export, it should work.
If you want replace that char while exporting data, you could use that formula : select case (position ('|' in MY_COL) (named MY_POS)) when 0 then MY_COL else substring(MY_COL from 1 for MY_POS - 1) || (REPLACE_CHARACTER) || substring(MY_COL from MY_POS + 1) end as MY_COL
PS : Only valid if you have no more than 1 (one) occurence of |