Embedding a carriage return into a column

Problem: Exporting an internal table to a text file requires that a 'newline' column contain a carriage return. Solution: The information in this article applies to: * InterBase 4.x * InterBase 5.x You could use a C/Delphi program to pass the ASCII character value (as a string) to the UPDATE statement, but here is a way using standard SQL. Assume that the table you wish to export (TABLE1) has two fields (ID INTEGER, NAME VARCHAR(10)) and some existing data: ID NAME === ========== 101 Name1 102 Name2 103 Name3 The first step is to add a column to hold a NEWLINE character (2 bytes): ALTER TABLE TABLE1 ADD NEWLINE CHAR(2); The next step is to create an external table that will hold the final exported data. The fields must correspond to those of the table to be exported (including the newly added NEWLINE field), be of type CHAR and wide enough to hold the the existing data: CREATE TABLE T1_EXT EXTERNAL FILE "C:T1_EXT.TXT" (ID CHAR(3), NAME CHAR(10), NEWLINE CHAR(2)); Next create a textfile (with an ASCII text editor such as Notepad) that contains a single carraige return. In this example assume that I have named this file "C:NL_EXT.TXT". The following CREATE statement will add the appropriate information to the system tables for later access: CREATE TABLE NL_EXT EXTERNAL FILE "C:NL_EXT.TXT" (NEWLINE CHAR(2)); INSERTING A CARRIAGE RETURN First, take a look at what table TABLE1 consists of: select * from table1 ID NAME NEWLINE =========== ========== ======= 101 name1 102 name2 103 name3 Then you issue the following SQL statement that takes the carriage return value in the newline external file (NL_EXT) and updates the NULL newline column in the internal table (TABLE1) with it. update TABLE1 set newline = (select newline from NL_EXT) Now TABLE1 has: select * from TABLE1 ID NAME NEWLINE =========== ========== ======= 101 name1 102 name2 103 name3 Notice that the NULL is missing from the NEWLINE column. That is because it contains a carriage return. Also, the carriage return value takes two characters on Windows platforms, one on UNIX. EXPORTING TO AN EXTERNAL TABLE/FILE Now, a simple insert statement to move the data from the internal table to the external table/file: insert into T1_EXT (ID, name, newline) select CAST(ID as char(4)), cast(NAME as char(10)), NEWLINE from TABLE1 Note that the integer column in T1_EXT was a char and you had to use the CAST operator to move the numeric value. Now, did the data go over? select * from T1_EXT ID NAME NEWLINE ====== ========== ======= 101 name1 102 name2 103 name3