How to strip a character in the data?

Tools
Enthusiast

How to strip a character in the data?

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.

Thank you for all your help.
Nishi
2 REPLIES
Enthusiast

Re: How to strip a character in the data?

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

Re: How to strip a character in the data?

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 |