meta data for this page
  •  

F_STR2EXCEL

function from adhoc

Entrypoint excelstring not compatible with UTF-8 - if needed please ask

Inputs/Outputs

 Input       CSTRING(32760)     String 
 Output      CSTRING(32760)     String changed for Excel

Syntax

 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 32760 characters (Limitation in Excel)
 TestSQL
 SELECT '"1.Zeile ""Paul"" und' || F_LF() || '2.Zeile"' AS ISCORRECT, F_STR2EXCEL('1.Zeile "Paul" und' || F_CRLF() || '2.Zeile') FROM RDB$DATABASE;
 SELECT NULL AS ISCORRECT, F_STR2EXCEL(NULL) FROM RDB$DATABASE;
 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.
 For instance:
 SELECT F_RIGHT(F_STR2EXCEL(STRFeldTAGEBUCH), 1000) FROM ... exportiert z.B. die letzten 1000 Zeichen des Tagebuchs.