Ask Your Question
0

How to change type of populated field in Base?

asked 2015-09-06 14:27:23 +0200

JKEngineer gravatar image

updated 2015-09-07 10:35:32 +0200

Alex Kemp gravatar image

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!

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
2

answered 2015-09-07 09:57:38 +0200

pierre-yves samyn gravatar image

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

edit flag offensive delete link more

Comments

@pierre-yves samyn 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

JKEngineer gravatar imageJKEngineer ( 2015-09-07 14:37:46 +0200 )edit
1

answered 2015-09-07 04:24:59 +0200

doug gravatar image

updated 2015-09-08 01:56:27 +0200

@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 @pierre-yves samyn 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

edit flag offensive delete link more

Comments

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!

JKEngineer gravatar imageJKEngineer ( 2015-09-07 05:31:14 +0200 )edit
1

answered 2015-09-06 23:34:00 +0200

peterwt gravatar image

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.

edit flag offensive delete link more

Comments

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

JKEngineer gravatar imageJKEngineer ( 2015-09-06 23:39:13 +0200 )edit

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.

charlie.it gravatar imagecharlie.it ( 2015-09-07 00:18:08 +0200 )edit

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

peterwt gravatar imagepeterwt ( 2015-09-07 14:35:49 +0200 )edit
1

answered 2015-09-06 15:23:59 +0200

updated 2015-09-06 23:13:36 +0200

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)

image description

edit flag offensive delete link more

Comments

@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 gravatar imageJKEngineer ( 2015-09-07 02:00:36 +0200 )edit

@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 gravatar imageJKEngineer ( 2015-09-07 02:04:47 +0200 )edit

... 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 gravatar imageJKEngineer ( 2015-09-07 02:09:54 +0200 )edit

... 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 gravatar imageJKEngineer ( 2015-09-07 02:15:10 +0200 )edit

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.

JKEngineer gravatar imageJKEngineer ( 2015-09-07 14:59:39 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-09-06 14:27:23 +0200

Seen: 1,183 times

Last updated: Sep 08 '15