Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

@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 column 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. 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

@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 column 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. 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

@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. 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