Dear Team
We use DB2 v10.5 and using DBArtisan tool
Can someone please guide how to convert digits to binary numbers using db2 feature.
Ex> for number 9 , binary should be 1001 ( 8+1)
Any help appreciated. Thanks
The following was tested on DB2 Express-C Version 11.1 on a Centos 7 Virtual Machine.
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$ cat -n dec2bin.sql
1 --
2 WITH inp(num) as (
3 -- Supply the input decimal number here
4 SELECT 9
5 FROM SYSIBM.SYSDUMMY1
6 ),
7 t(num, bit, iter, bin) AS (
8 -- This is a recursive SQL that keeps dividing inp.num by 2 until it is
9 -- reduced to 0. The bit column is the remainder (either 0 or 1) per
10 -- iteration. Each bit column is prepended successively and set in the bin
11 -- column. Eventually, when num is 0, the recursion ends and we have the
12 -- binary representation of inp.num in the bin column.
13 SELECT num, NULL, 0, CAST('' AS VARCHAR(40))
14 FROM inp
15 UNION ALL
16 SELECT num/2, num%2, iter+1, STRIP(num%2 || bin)
17 FROM t
18 WHERE iter < 1000
19 AND num > 0
20 )
21 SELECT inp.num AS dec,
22 t.bin AS bin
23 FROM inp, t
24 WHERE t.num = 0
25 ;
26
27
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$ db2 -tmf dec2bin.sql
DEC BIN
----------- ----------------------------------------
9 1001
1 record(s) selected.
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$
And another test with a larger input number:
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$ cat -n dec2bin.sql
1 --
2 WITH inp(num) as (
3 -- Supply the input decimal number here
4 SELECT 786955301775
5 FROM SYSIBM.SYSDUMMY1
6 ),
7 t(num, bit, iter, bin) AS (
8 -- This is a recursive SQL that keeps dividing inp.num by 2 until it is
9 -- reduced to 0. The bit column is the remainder (either 0 or 1) per
10 -- iteration. Each bit column is prepended successively and set in the bin
11 -- column. Eventually, when num is 0, the recursion ends and we have the
12 -- binary representation of inp.num in the bin column.
13 SELECT num, NULL, 0, CAST('' AS VARCHAR(40))
14 FROM inp
15 UNION ALL
16 SELECT num/2, num%2, iter+1, STRIP(num%2 || bin)
17 FROM t
18 WHERE iter < 1000
19 AND num > 0
20 )
21 SELECT inp.num AS dec,
22 t.bin AS bin
23 FROM inp, t
24 WHERE t.num = 0
25 ;
26
27
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$ db2 -tmf dec2bin.sql
DEC BIN
-------------------- ----------------------------------------
786955301775 1011011100111010001100001111001110001111
1 record(s) selected.
[db2inst1@r2d2-centos7 data]$
[db2inst1@r2d2-centos7 data]$
2 Likes
Hi durden_tyler
Tanks a lot . Works fine