Replacing Text in a Blob Column

Replacing text in a BLOB Column

REPLACE works on the following datatypes:

Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

You have chosen to store character data as a collection of bytes (BLOB). These can not be worked on directly because a BLOB has no context and is only a very very big number. It can't be converted to characters without your input: you need its character set to convert binary data to text.

You'll have to either code the function REPLACE yourself (using DBMS_LOB.instr for instance) or convert your data to a workable CLOB and use standard functions on the CLOB.

I would advise strongly to change the datatype of your column. This will prevent any further character set conversion error you will likely run into in the future.

If you really want to work with blobs, use functions like these:

SQL> CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
  2     l_clob         CLOB;
  3     l_dest_offset  NUMBER := 1;
  4     l_src_offset   NUMBER := 1;
  5     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  6     l_warning      NUMBER;
  7  BEGIN
  8     dbms_lob.createtemporary(l_clob, TRUE);
  9     dbms_lob.converttoclob(dest_lob     => l_clob,
 10                            src_blob     => l_blob,
 11                            amount       => dbms_lob.lobmaxsize,
 12                            dest_offset  => l_dest_offset,
 13                            src_offset   => l_src_offset,
 14                            blob_csid    => nls_charset_id('AL32UTF8'),
 15                            lang_context => l_lang_context,
 16                            warning      => l_warning);
 17     RETURN l_clob;
 18  END convert_to_clob;
 19  /

Function created

SQL> CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
  2     l_blob         BLOB;
  3     l_dest_offset  NUMBER := 1;
  4     l_src_offset   NUMBER := 1;
  5     l_lang_context NUMBER := dbms_lob.default_lang_ctx;
  6     l_warning      NUMBER;
  7  BEGIN
  8     dbms_lob.createtemporary(l_blob, TRUE);
  9     dbms_lob.converttoblob(dest_lob     => l_blob,
 10                            src_clob     => l_clob,
 11                            amount       => dbms_lob.lobmaxsize,
 12                            dest_offset  => l_dest_offset,
 13                            src_offset   => l_src_offset,
 14                            blob_csid    => nls_charset_id('AL32UTF8'),
 15                            lang_context => l_lang_context,
 16                            warning      => l_warning);
 17     RETURN l_blob;
 18  END convert_to_blob;
 19  /

Function created

You can call these functions directly from SQL:

SQL> UPDATE ape1_item_version
  2     SET DYNAMIC_DATA = convert_to_blob(
  3                          REPLACE(convert_to_clob(DYNAMIC_DATA),
  4                                 'Single period',
  5                                 'Single period period set1')
  6                          )
  7   WHERE NAME = 'PRIT ALL POOL for Duration Telephony 10_NA_G_V_H_N_Z2';

1 row updated

Replace a word in BLOB text by MySQL

Depends what you mean by "replace"; using replace to show modified text in select:

select replace(slogen, 'bananas', 'apples') from tcountriesnew where slogen like '%bananas%';

Or update data in a table:

update tcountriesnew set slogen=replace(slogen, 'bananas', 'apples') where slogen like '%bananas%';

BTW. Why are you using blob for text? You should use text type for textual data and blob for binary data.

replace blob with random string

The maximum length of a VARCHAR2 in SQL is 4000 bytes in Oracle 11g. Any row you attempt to run this statement against that has more than 3987 bytes (4000 minus 13 for the <edit></edit> tags) will result in an error as you will exceed the 4000 byte limit.

You can either:

• Cap the length to 3987 like:
  DBMS_RANDOM.STRING('X', GREATEST(length(info), 3987))
• Or use PL/SQL and the dbms_lob package to build up the desired blob. PL/SQL has a maximum VARCHAR2 length of 32767 bytes but also allows for looping constructs that would allow you to loop over the length(info) in 32767 chunks and appending data to a lob variable using dbms_lob.append.

edit: Use GREATEST rather than MAX.

How to replace a word in a blob with the next blob entry of the database table

OK, so from what I can gather, you've got some rows in this table which are actual content that you want to display, including references to some image filenames, then in the same table you have these actual images stored as base64 encoded BLOBs.

Impossible for me to test... so have a play around with it, and see if it offers what you're interested in.

I've commented my additions to make them more clear.

Oh and also I've used <?= shorthand purely because I think it makes the code a bit easier to read/more accessible. You obviously don't need to use this if you don't want to!

<?php

<<PUT ANY OTHER EXISTING CODE HERE, E.G. THE START OF YOUR SCRIPT>>
$res = mysqli_query($link, createRequest(true, $link)) or die(mysqli_error($link));

// This function serves a dual purpose
// Given an image filename, e.g. `image001.png`, it will search the table for this filename and (hopefully) return a base64 encoded BLOB
// It then decodes this content and returns it
function get_image_from_filename ($filename, $db_connection) {
    $query = "SELECT article_attachment.content FROM article JOIN ticket ON article.ticket_id = ticket.id JOIN article_attachment on article.id = article_attachment.article_id WHERE article_attachment.filename = '$filename'";

    $res = mysqli_query($db_connection, $query) or die(mysqli_error($db_connection));

    while ($row = mysqli_fetch_assoc($res)) {
        return base64_decode($row["content"]);
    }
}

while($row = mysqli_fetch_assoc($res)) {

    $title = $row["a_subject"];
    $time = $row["create_time"];
    $sender = $row["a_from"];
    $reciever = $row["a_to"];
    $body = $row["content"];
    $articleid = $row["id"];

    if (base64_encode(base64_decode($body, true)) === $data){
        // If the `$body` can be decoded/encoded successfully, this row is an image - let's skip it
        continue;
    }
    else {
        // Otherwise, let's find all of the references to our images, and look them up in the database

        // This pattern will find the CID references and retrieve the associated image's filename
        $pattern = '/(?:cid:)([a-zA-Z0-9]+\.[a-zA-Z0-9]+)(@[a-zA-Z0-9]+\.[a-zA-Z0-9]+)+/';
        // This is the replacement that will be put into the `$body` variable, using the function at the top of this script
        $replacement = "<pre>" . '<img src="data:image/jpeg;base64,' . get_image_from_filename('$1', $link) . '" />' . "</pre>";

        $body = preg_replace($pattern, $replacement, $body);

?>

<tr>
    <td><?=$time?></td>
    <td><?=$sender?></td>
    <td><?=$receiver?></td>
    <td><?=$title?></td>
    <td><?=$body?></td>
    <td><a href="http://localhost/php/imageviewer.php?ArticleID=<?=$articleid?>">Zeige Anhänge</a></td>
</tr>

<?php

    } // End base64 if

}

Delete empty lines in a text blob

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), '')

How do I get textual contents from BLOB in Oracle SQL

First of all, you may want to store text in CLOB/NCLOB columns instead of BLOB, which is designed for binary data (your query would work with a CLOB, by the way).

The following query will let you see the first 32767 characters (at most) of the text inside the blob, provided all the character sets are compatible (original CS of the text stored in the BLOB, CS of the database used for VARCHAR2) :

select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';

---

Related Topics