|
View previous topic :: View next topic
|
| Author |
Message |
sun_job
New User
Joined: 18 Sep 2007
Posts: 73
Location: Bangalore
|
|
|
|
My requirement is to have two characters in column.
Here is the scenario :-
My table column is a int(2) which contains values ranging from 0(null) to 2 I want to have for values whose length is less than 2 should be padded with zero
example:-
null should be updated to 00
1 should be updated to 01
12 should be updated to 12
Help would be really appreciated.
Tried with LPAD function , but not working...
An expert help is appreciated |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
| Tried with LPAD function , but not working... |
Share us the query you had used. |
|
| Back to top |
|
|
sun_job
New User
Joined: 18 Sep 2007
Posts: 73
Location: Bangalore
|
|
|
|
| Code: |
SELECT
CHAR(DEPT_I) AS DEPTID,
LPAP(CHAR(CLA_I),2,'0') AS CLASSID
FROM DB2PD.CLASS;
|
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| And would it be possible for you to reveal to us the manner in which it "does not work"? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Sorry to not say it first time
Please share the output yo get and also the ouput you expect for the set of data in table |
|
| Back to top |
|
|
sun_job
New User
Joined: 18 Sep 2007
Posts: 73
Location: Bangalore
|
|
|
|
It seems LPAD function does not work. I have tried to do it with CASE expression.
| Code: |
(CASE
WHEN LENGTH(LTRIM(RTRIM(CHAR(C.CLA_I))))=1 then '0'||LTRIM(RTRIM(CHAR(C.CLA_I)))
WHEN LENGTH(LTRIM(RTRIM(CHAR(C.CLA_I))))=2 then LTRIM(RTRIM(CHAR(C.CLA_I)))
END) AS CLASSID, |
This is working as expected |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi,
Check for the function DIGITS in DB2 Manual
You may need to handle Null values using CASE statement
Regards,
Chandan |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
afaik there is no such thing as int(2) in DB2 but you could just use :
| Code: |
| right(digits(i2),2) |
or if i2 is nullable :
| Code: |
| right(digits(coalesce(i2,0)),2) |
|
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Thanks Guyc
I have totally missed coalesce while suggesting CASE statement in my earlier post
Regards,
Chandan |
|
| Back to top |
|
|
sun_job
New User
Joined: 18 Sep 2007
Posts: 73
Location: Bangalore
|
|
|
|
| chandan.inst wrote: |
Hi,
Check for the function DIGITS in DB2 Manual
You may need to handle Null values using CASE statement
Regards,
Chandan |
Sure Chandan , will check the same |
|
| Back to top |
|
|
|
|
