How do I left pad a database column in FrontBase?

Question

I have a table of items (let's call it WIDGET) that each have their own eight-digit barcode numbers stored in a VARCHAR field (let's call it BARCODE; the table's primary key is in a separate integer column ID). My problem is that some rows include the leading zeros, while others do not. I would like to update all the existing records (several hundred in all) to eight digits for the sake of consistency.

• 1 → 00000001
• 234 → 00000234
• 5678 → 00005678
• 00009012 → 00009012

I know FrontBase is SQL-92 compliant, but SQL-92 has no function specifically for left-padding strings. I already came up with a solution, but I am posting this question to see if anyone can think of a better way of doing this than I did.

Answer 1

This seems to work (at least in FrontBase 5.2.9):

UPDATE WIDGET SET BARCODE =
SUBSTRING('00000000' || BARCODE FROM CHAR_LENGTH(BARCODE) + 1);