hi
what derivation should i use to convert linebreaks in a single column into a delimiter
e.g.
ive got a blob colum with
.605632211.605634110.605634111.605634118.
.605634119.605634240.605634246.605634348.
.605634447.605635306.605636017.605636043.
but i want to change it into a single line with comma as the delimiter?
.605632211.605634110.605634111.605634118.,.605634119.605634240.605634246.605634348.,.605634447.605635306.605636017.605636043.
any help greatly appreciated
how to convert linebreaks in a single field to a delimiter
Moderators: chulett, rschirm, roy
You can use the either CONVERT or EREPLACE functions to replace character sequences in a string. What command you use depends upon what you have in your file as a line break (i.e. is it a single UNIX style LF or a <cr><lf>). Essentially your command will look like EREPLACE(BlobColumn,CHAR(nn),',') EREPLACE(BlobColumn,CHAR(nn):CHAR(nn),',') where the nn is replaced by 135(for CR), 12 or 13 (for LF and VT respectively).
</a>Hexa,
the EREPLACE function will work, but you really need to know which character you have in your input column (the blob) that is causing the split into multiple lines. Typically in Unix you will have just a lf, while a DOS type file will have <cr><lf>. So your first step is to find out exactly WHICH type you have in your blob. If you don't have a binary type editor that will show you non-displayable characters then you could write a quick job to read this column and output SEQ(In.BlobColumn[42,1]) {looking at your sample it seems that you have 4 groups of 9 digits with a ".".
What you could also do is the following derivation:
OCONV(EREPLACE(InBlobColumn,'.','&'),'MCP')
What this statement will do is replace all "." by "&" in your field, then do an unprintable conversion which will replace all unprintable characters by ".". This will at least show you how many unprintables you have.
The derivation:
CONVERT(TRIM(OCONV(EREPLACE(InBlobColumn,'.','&'),'MCP'),',','D'),'.&',',&')
Will achieve what you are trying to do, but is very inefficient and you are better off finding out what your separators are.
the EREPLACE function will work, but you really need to know which character you have in your input column (the blob) that is causing the split into multiple lines. Typically in Unix you will have just a lf, while a DOS type file will have <cr><lf>. So your first step is to find out exactly WHICH type you have in your blob. If you don't have a binary type editor that will show you non-displayable characters then you could write a quick job to read this column and output SEQ(In.BlobColumn[42,1]) {looking at your sample it seems that you have 4 groups of 9 digits with a ".".
What you could also do is the following derivation:
OCONV(EREPLACE(InBlobColumn,'.','&'),'MCP')
What this statement will do is replace all "." by "&" in your field, then do an unprintable conversion which will replace all unprintable characters by ".". This will at least show you how many unprintables you have.
The derivation:
CONVERT(TRIM(OCONV(EREPLACE(InBlobColumn,'.','&'),'MCP'),',','D'),'.&',',&')
Will achieve what you are trying to do, but is very inefficient and you are better off finding out what your separators are.