F_STR2EXCEL

<< F_DIGITS | IBExpert UDF Functions | F_RC >>

F_STR2EXCEL

 
  • function from adhoc
  • Compatible with UTF-8
  • Input VARCHAR(32760) String
  • Output VARCHAR(32760) String changed for Excel

To convert multiline texts and texts with converted commas to Excel, it is required to transform the string. This function will do the following: - appends a double inverted comma at the beginning and the end of the string - doubles all inverted commas - deletes all CHR(13) in the string - limits the input string to 8190 characters (Limitation in FB UTF8)

Note: Actually it doesn’t make any sense to export a very large text from the database to excel cell, this function may be practicable in combinaton with F_LEFT or F_RIGHT.

Example

 SELECT '"1.Line "John" and' || F_CRLF() || '2.Line"' AS ISCORRECT, F_STR2EXCEL('1.Line "John" and' || F_CRLF() || '2.Line') FROM RDB$DATABASE;

Expected results:

 =========================== ===============================================
 "1.Line "John" and
 2.Line" "1.Line ""John"" and
 2.Line" 

Example

 SELECT NULL AS ISCORRECT, F_STR2EXCEL(NULL) FROM RDB$DATABASE;

Expected results:

 ISCORRECT F_STR2EXCEL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
 ========= ===============================================================================
 <null>    <null>                 

back to top of page
<< F_DIGITS | IBExpert UDF Functions | F_RC >>