How to transform a blob field into string lines (varchar) in SQL Firebird?

Hello guys.

In some cases we have BLOB fields in the databases for storing texts.

There are times when it is important to retrieve line by line from this BLOB that acts as
a StringList or Memo.

Cool, but how to do?

To help, we created a procedure that converts a BLOB of plain text (multi lines)
into individual lines.

Let’s go to the code!

CREATE OR ALTER PROCEDURE UTIL_SPLIT_BLOB_TO_ROW (
    BLOBTEXT VARCHAR(1000))
RETURNS (
    MNUMBER INTEGER,
    RETSTRING VARCHAR(1000))
AS
DECLARE variable LSTRING VARCHAR(1000);
DECLARE variable POS1 INTEGER;
DECLARE variable POS2 INTEGER;
DECLARE variable INTLEN INTEGER;
BEGIN
  lstring = SUBSTRING( BLOBTEXT FROM 1 FOR 1000);
 
  pos1 =  1 ;
  intlen = CHAR_LENGTH(lstring);
  MNUMBER = 0;
 
  while (pos1 < intlen) do
  BEGIN
    MNUMBER = MNUMBER + 1;
    pos2 = POSITION (ASCII_CHAR(13) IN SUBSTRING(lstring FROM pos1));
 
    IF (pos2 = 0) THEN
    BEGIN
      pos2 = intlen;
    END
 
    retstring = SUBSTRING (lstring FROM pos1 FOR (pos2 - 1));
    pos1 = pos2 + pos1;
 
    suspend;
  END 
END

In the procedure we are using varchar (1000) of parameters, but this can be adapted to your reality.

Now for the usage example:

SELECT
U.MNUMBER,
U.RETSTRING
FROM UTIL_SPLIT_BLOB_TO_ROW(:text) U

By entering the parameter as an example:
DELPHIFAN FORUM
BEST DELPHI COMMUNITY IN WORLD

the return will be

MNUMBER RETSTRING
1 DELPHIFAN FORUM
2 BEST DELPHI COMMUNITY IN WORLD

In conclusion, with a simple procedure it is possible to transform a multi-line text field
into SQL return lines.

I hope it will be useful to everyone! Regards and see you next post.