Integer Value Right Padding in SQL

Question

Step1: I have a table called XYZ which contains following integer columns:

 ID      A          B          C
 1    201507    20150810   20150311
 2    20150812  201509     201510

I need to write a SQL query where if any values of A, B, and C is smaller than 8 digits then I need to consider it as 8 digits by adding zeros to the right of the value for step2 (I am not allowed to update the table.). For example:

 ID      A            B          C
 1    20150700    20150810   20150311
 2    20150812    20150900   20151000

How to add zeros to the right of the integer values through SQL query?

Step 2: I need to find for each record A<B, B<C or not. Please let me know how to write the query. I am using PostgreSQL. Thank you.

Are you allowed to use trigger? If yes, a before insert trigger can be used to check the value of insert and multiply by 10 until it reaches 8 digits. — lamandy
– lamandy, Commented Dec 6, 2017 at 5:54

santoshkumar Pithani · Accepted Answer · 2017-12-06 06:38:27Z

2

SELECT CAST(2015 AS VARCHAR(10))+REPLICATE('0',8-LEN(2015))

SELECT  2015 *(CAST('1'+REPLICATE('0',8-len(2015))  AS INT))

edited Dec 6, 2017 at 6:38

answered Dec 6, 2017 at 6:01

santoshkumar Pithani

1219 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

S M Shamimul Hasan Over a year ago

Thanks. I am not allowed to change the type to VARCHAR. Values should be an integer.

santoshkumar Pithani Over a year ago

Hi, i updated solution as you like without converting column as varchar

user330315 · Accepted Answer · 2017-12-06 06:58:05Z

1

You can use rpad() to add trailing zeros, then cast the result back to an integer:

select id, 
       rpad(a::text, 8, '0')::int, 
       rpad(b::text, 8, '0')::int, 
       rpad(c::text, 8, '0')::int
from the_table;

To avoid repeating the expressions, use a derived table:

select *
from (
  select id, 
         rpad(a::text, 8, '0')::int as a, 
         rpad(b::text, 8, '0')::int as b, 
         rpad(c::text, 8, '0')::int as c
  from the_table
) t
where a < b or b < c --<< change that to the condition you want

answered Dec 6, 2017 at 6:58

user330315

Comments

Jayasurya Satheesh · Accepted Answer · 2017-12-06 06:18:07Z

1

just try this

select
ID,
A = LEFT(cast(a as varchar(100)+'00000000',8),
b = LEFT(cast(b as varchar(100)+'00000000',8),
C = LEFT(cast(c as varchar(100)+'00000000',8)
from xyz

edited Dec 6, 2017 at 6:18

answered Dec 6, 2017 at 5:56

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

2 Comments

Michał Turczyn Over a year ago

If columns are integers, zeros will be implicitly converted to compatible type (int), so it will be converted to zero, so a+'00000000' = a

Jayasurya Satheesh Over a year ago

@MichałTurczyn I have added a cast statement

Michał Turczyn · Accepted Answer · 2017-12-06 06:22:53Z

1

Try this:

select cast(left(cast(A as varchar(20)) + '00000000', 8) as int) as [A],
       cast(left(cast(B as varchar(20)) + '00000000', 8) as int) as [B],
       cast(left(cast(C as varchar(20)) + '00000000', 8) as int) as [C]
from TABLE_NAME

If you want to avoid any casting, this might be solution:

select case when 8 - LEN(A) > 0 then A * Power(10, (8 - LEN(A))) else A end as [A],
       case when 8 - LEN(B) > 0 then B * Power(10, (8 - LEN(B))) else B end as [B],
       case when 8 - LEN(C) > 0 then C * Power(10, (8 - LEN(C))) else C end as [C]
from MY_TABLE

edited Dec 6, 2017 at 6:22

answered Dec 6, 2017 at 6:11

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Integer Value Right Padding in SQL

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related