Oracle LPAD Function in Derby

Recently I had the opportunity of working with Derby 10.9 and I needed to convert some queries written in Oracle to Derby, and I hit this wall; Oracle has this lpad function which can add zeroes to the left of a string e.g.

lpad('tech', 8, '0'); would return '0000tech'

Basically what the above meant is you pass in a string called "tech", it will fill up from the left with 0s, and make sure the final string is of length 8.

So how the heck do I make it work the same way in Derby? Took me one or two hours to figure it out...

ij> select myname from mytabletest;
MYNAME
----------
tech

ij> select substr('00000000'||myname, length(myname)+1) from mytabletest;
1
------------------
0000tech

As you can see, there are 8 zeroes, you adjust it according to the final length of the string, so if you want a longer length with more zeroes, just add more zeroes. By the way, the myname field is a varchar(10).

So what happens if myname is a number? We look at myid which is a decimal (10,0).

ij> select myid from mytabletest;
MYID
------------
12345

So if you run the same query, you will get an error.

ij> select substr('00000000'||myid, length(myid)+1) from mytabletest;
ERROR 42846: Cannot convert types 'DECIMAL' to 'VARCHAR'.

Try this, just cast myid as a char, depending on the size, as mentioned myid is size 10, so just put char(10).

ij> select substr('00000000'||rtrim(cast(myid as char(10))), length(rtrim(cast(myid as char(10))))+1) from mytabletest;
1
------------------
00012345