How to change type of populated field in Base?

[JKEngineer]

I have a table in Base ( Win 7, LO Portable v 4.3.2.2, US English) that is populated. It has 5 digit zip codes in it. I initially set it as Integer, not realizing that leading 0’s would be removed by the database engine. It now has about 1500 records in it. I need to change the field type, probably to Text. How best to do it? What type would work best?

If I try to edit the table and change the type I get an error that it can’t be done and would I like to remove the field and append a new one. Accepting that wipes out the data in the field.

I think an approach would be to created a new field, with a better choice of field type and a similar name. How would I then populate it with the existing data? If I can somehow copy it over, I can easily look for the 4 digit entries and prepend a 0 to them manually. I’m guessing this may need some (simple) SQL. If so, can someone tell me the SQL?

Also, I am assuming that once I have created the new field, populated it, and edited it, I can then delete the original and rename the replacement to match the original. Am I correct in thinking that all existing references, such as in queries and reports to the original field will now point to the renamed new field?

TIA!

[PYS]

Hi

Another solution with Tools SQL to add data with leading zeroes in a 5 digit ZIP text field:

UPDATE "zipTbl"
SET "ZIP" = RIGHT('00000' || "zipint", 5) 

• Concatenating 5 zeros before the number
• Keep the last 5

Regards

[JKEngineer]

@PYS Thanks for this. It enabled me to pick up where I had gotten to with the others’s help, which was a Zip field as text, with the original name, in the original field order. I prepended a single 0 to the 4 digit zips. I did not use your exact form, since I think it would have created 00000 zips where the zip was missing (blank). I used:

UPDATE "tbl_Contacts-rework"

SET “Contact Zip” = RIGHT(‘0’ || “Contact Zip”, 5)

Note I was able to overwrite the same field

[charlie.it]

Hi, you can open a sheet of Calc, push F4 and open your database in the new window.

Then drag left-top corner in the sheet below and drop it.

In the first cell of new column of the sheet, write: “=IF(LEN(B2)=4;CONCATENATE(“0”;B2);B2)”.

Copy it in the cells below.

Hide the ZIP column.

Rename ZIP the new column.

Select ID and new ZIP column and copy those.

Open your database and paste the select in table area, assign a name and save the new table.

(Excuse me for my bad English and for Italian screenshot)

[JKEngineer]

@charlie.it I previously posted a comment here, it is now missing. It said: Thanks for both the answer and for editing it to make it clearer. I will try it and let you know how it works. (It was my first comment on this thread.)

[JKEngineer]

@charlie.it @peterwt Here’s my initial results. I was not able to open the table in the F4 window as shown by charlie. However, I opened the database file and the table within it and did a similar drag from there. I made a new column next to the old Zip column. I renamed the original to “Zip old” and named the new column to Zip. I applied the formula as suggested by charlie. It prepended the 0, but the zips with the prepended 0 are left aligned (are text) and the originals are …more

[JKEngineer]

… right aligned (numbers). Selecting the column or some of the contents and formatting cells as Text does not affect the display.
I deleted the old Zip column. I copied the new Zip column and pasted it over itself as text and numbers only.

I am not clear on charlie’s instructions for then getting the new columns into the database. The Calc sheet has columns for all the fields of the table. …more

[JKEngineer]

… I tried copying the entire Calc sheet and pasting it, and tried copying the Zip column and pasting that, but the paste has no effect in Base. Note that the Table has both a native ID and a foreign ID field.
I’d appreciate some clarification - both on charlie’s import instructions and on @peterwt 's convert to text instructions. .

[JKEngineer]

I have discovered that the Base Table opened by dragging into the Calc window, as above, was short. It had about 1500 records vs the full table at about 1800 records.

[peterwt]

As you said to preserve leading 0 use a Text Field.

Follow the method proposed by @Charlie.it but before you copy the amended sheet change the Format of the new ZIP column created by the formula to Text. Then copy and paste into Base.

[JKEngineer]

@peterwt Thanks. I’m not sure quite where I should change the format of the new column. I’ll experiment and advise of results.

[charlie.it]

I think it is not necessary to change the format of the column. In my testing the new table is pasted with the type text for field ZIP.

[peterwt]

Yes @charlie.it you are right - when pasting into Base all Fields default to Text.

[doug]

@charlie.it and @peterwt provide good solutions. To do this in pure SQL is not that difficult. To access the necessary feature, go to Tools → SQL as these commands will not work in the normal query editor. Please note that the following queries are benign as written, but any UPDATE or ALTER TABLE query is potentially data-destructive so test and backup before using on live data, especially if you are not sure you understand what it does.

This query adds a VARCHAR zip code column to an existing table called zipTbl.

ALTER TABLE "zipTbl" ADD COLUMN "ziptxt" VARCHAR (12);

This query transfers the data from the existing INTEGER column zpint in zipTbl to the new ziptxt column, with appropriate formatting for a five-digit zip. Other database engines provide elegant ways to format an integer in a string, HSQLDB apparently does not (edit although @PYS provided an elegant workaround subsequent to this post). Thus, I have kludged together a series of CASE WHEN statements that test the length of the integer when converted to text, and append the right number of zeros for a five-digit zip.

UPDATE "zipTbl" 
SET "ziptext" = CONCAT(
  CASE WHEN LENGTH("zipint") = 1 THEN '0000' ELSE
  CASE WHEN LENGTH("zipint") = 2 THEN '000' ELSE
  CASE WHEN LENGTH("zipint") =3 THEN '00' ELSE
  CASE WHEN LENGTH("zipint") =4 THEN '0' ELSE
  '' END END END END
, "zipint")

To fully access the changes after you are done, you should click on the TABLES tab in the database viewer and then in the menu VIEW → REFRESH TABLES

[JKEngineer]

This looks like it will work. I munged it a little, so will have to start over. I’ll look again in the morning. Thanks!
I did succeed in generating the Text zip code field. Then did some renaming to end up with the original field deleted and the Text version named to the original’s name. I did not use the Case statements - manually edited the 4 digit codes. When I reopened the table the codes were not changed and the field location was in the original’s order, not at the end!