Convert the stno values into a 6-digit string with leading zeros using Oracle, MySQL, PostgreSQL, MySQLServer

#SqlInterviewquestions

Convert the stno values into a 6-digit string with leading zeros using
Oracle, MySQL, PostgreSQL, MySQLServer

SOURCE		TARGET
ID	STNO	ID	STNO
1	8	1	000008
2	23	2	000023
3	704328	3	000328
4	854	4	000854
5	1234	5	000234
6	8987	6	000987

ORACLE:

with cte as (
select 1 id,8 as stno from dual union all
select 2 id,23 as stno from dual union all
select 3 id,704328 as stno from dual union all
select 4 id,854 as stno from dual union all
select 5 id,01234 as stno from dual union all
select 6 id,8987 as stno from dual
)
select id,stno,
case
when length(stno)<3 then lpad(stno,6,0)
else lpad(substr(stno,-3),6,0) end as result
from cte;

Note :substr(stno,-3) will take last 3 digits

-- PostgreSQL

select id,stno,
case

when
length(stno)<3 then RIGHT('000000' || CAST(stno AS VARCHAR(6)), 6)
else RIGHT('000000' || CAST(right(stno,3) AS VARCHAR(6)), 6) end as result
from cte;

-- SQL Server
select id,stno,
case

when
len(stno)<3 then RIGHT('000000' + CAST(stno AS VARCHAR(6)), 6)
else RIGHT('000000' + CAST(right(stno,3) AS VARCHAR(6)), 6) end as result
from cte;