CODEWITHSUNDEEP
sqlpostgresql
S M Shamimul Hasan
S M Shamimul Hasan

Reputation: 6644

Integer Value Right Padding in SQL

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.

Upvotes: 1

Views: 176

Answers (4)

user330315
user330315

Reputation:

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

Upvotes: 1

santoshkumar Pithani
santoshkumar Pithani

Reputation: 121

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

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

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37347

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

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 1

Related Questions