Delete empty lines in a text blob

Question

I have a list of email addresses in a table called cc_list (blob(text)). To remove an email address from the list, I have used the replace function

update actions 
set cc_list=replace(cc_list,'email@bob.com','') 
where contact_id=85

Now, the list shows as

email
email

email
email

In the GUI, I just see the empty lines at the top. I have tried the trim option removing the carriage return (ascii_char(9))

trim(leading from replace (cc_list, ascii_char(9),''))

and

replace(cc_list,ascii_char(9),'')

I still see the empty lines.

What can I do to fix this?

A cariage return is not the same as `ascii_char(9)` I Think that should be `ascii_char(13)`, see: https://theasciicode.com.ar/ascii-control-characters/carriage-return-ascii-code-13.html — Luuk, Aug 28 '21 at 11:21
@Luuk it is actually yet more different! DOS & OS/2 & Windows it would be a PAIR of character 13, then 10. For UNIX (including MacOS X) it would be 10 alone, without 13. For MacOS classic that would be 13 without 10. — Arioch 'The, Sep 01 '21 at 10:48
@Arioch'The: No, CR (carriage return) is always char(13), and LF (line feed) is always char(10), and CRLF is a combination of those two characters. — Luuk, Sep 01 '21 at 18:03
@Luuk ah, okay, this is correct. I was proceeding from `see the empty lines at the top` so i was talking about ALL the possible markers of end of line, which can be CR or LF or CR/LF - and thus all of them to be cared for. Granted in this simplistic case it would probably be enough just to independently delete all CRs and all LFs — Arioch 'The, Sep 01 '21 at 23:11

Mark Rotteveel · Accepted Answer · 2021-08-28T13:04:27.147

The obvious solution would be to normalize your database and not store a list of things in an unstructured datatype like a blob, but instead use a many-to-one solution to store email addresses.

The reason your replace leaves a line break, is because you only replace the email address, so replacing <address2> in <address1><LF><address2><LF><address3> by an empty string leaves you with <address1><LF><LF><address3>.

The reason trim(leading ...) doesn't work, is because that only works for white space at the start of the blob, your line break is in the middle of the blob, in addition, by default trim only trims spaces (character 32).

The reason replace(..., ascii_char(9), '') doesn't work is because character 9 is a TAB, not a linefeed (LF, character 10), nor a carriage return (CR, character 13). In addition, attempting this replacement for only single line break would remove all line breaks from the blob, making your email addresses invalid as they would all end up on a single line.

Assuming your blob only contains linefeed (LF) (and not carriage return (CR) or CRLF), fixing the already broken blobs can be done by replacing all occurrences of two consecutive linefeed character with a single linefeed:

replace(cc_list, x'0a0a', x'0a')

(use x'0d0d', x'0d' for CR, or x'0d0a0d0a', x'0d0a' for CRLF)

or (if you're using a Firebird version that does not support hexadecimal literals):

replace(cc_list, ascii_char(10) || ascii_char(10), ascii_char(10))

Moving forward, you should attempt to replace an email address followed by a line break by an empty string. Note that this assumes that the last email address in a list is also followed by a line break:

replace(cc_list, 'email@bob.com' || ascii_char(10), '')

update message_actions set cc_list = replace(cc_list, 'bob@email.com',''); update MESSAGE_ACTIONS set cc_list=replace(cc_list ,x'0d0a0d0a', x'0d0a') — L_GIB, Aug 28 '21 at 14:11
@LeoFazzi You can do it with a single update using: `update message_actions set cc_list = replace(cc_list, 'bob@email.com' || x'0d0a', '');` — Mark Rotteveel, Aug 28 '21 at 14:34

Delete empty lines in a text blob

1 Answers1