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 protected]','') 
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?

Answer 1

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 protected]' || ascii_char(10), '')