TRIM Carriage Return/Line Feed

[baycolor]

Hi,

I have a VARHCAR(1000) column with different length strings in it.

Column A
ABC<cr/lf>243808<cr/lf>asd kljlkj<cr/lf>Seqid: 0012<cr/lf>
343969<cr/lf>2403<cr/lf>poiokjlk<cr/lf>Seqid: 0012<cr/lf>
AC<cr/lf>808<cr/lf>k<cr/lf>Seqid: 0012<cr/lf>

Last string on every row before the final <cr/lf> is always
Seqid: ########

Unknown number of ######## digits. I want to strip out
the digits. I've used the POSITION function along with the SUBSTRING function to get values like:
0201<cr/lf>
12234566<cr/lf>
1<cr/lf>

But now I need to get rid of the <cr/lf>.

NOTE: I'm doing this in SQL not exporting the data or anything. I was looking for a way to do this with TRIM TRAILING but can't seem to get it.

Does anyone know the code/syntax for doing this. I'm not great with HEX etc.

The following gets my current values:

SUBSTRING(comment_text FROM POSITION('seqid' IN comment_text) + 7)

I've been trying stuff like:
,TRIM(TRAILING '000D'X FROM SUBSTRING(comment_text FROM POSITION('seqid' IN comment_text) + 7))

but I get messages like "The PAD expression for TRIM has an incorrect data type or length".

Thanks in advance.

 

[dnoeth]

Use '0D'XC instead of '0D'X:

TRIM(TRAILING '0D'XC FROM
TRIM(TRAILING '0A'XC FROM
SUBSTRING(comment_text FROM
POSITION('seqid' IN comment_text) + 7)))

Dieter

 

[baycolor]

Thank you that worked great. And that solution will be so helpful since a lot of our data is fed into our dbase with a carriage returns and line feeds.