winsoftech
asked on
Pad zero to right sidein sql
I have column in my table pin_code whose size must be 6 chars ,but there are some values present in tbale with less that 6 chars.It have 2,3,4 or 5 chars values,i WANT TO MIGRATE THOSE VALUES ,Want to add zero to right side according to therir current content
Fo example
if one row in column have pin _code as "1" now want to migrat it as "100000" want pass 5 zero to right side
I want migrate the data for all rows who may have 2char pin code,3,4 or 5
Plz suggest me some function ,or common way to migarte all values in table
Fo example
if one row in column have pin _code as "1" now want to migrat it as "100000" want pass 5 zero to right side
I want migrate the data for all rows who may have 2char pin code,3,4 or 5
Plz suggest me some function ,or common way to migarte all values in table
select * from t_clnt_address
case when len(pin_code)=1 then pin_code+'00000'
when len(pin_code)=2 then pin_code+'0000'
when len(pin_code)=3 then pin_code+'000'
when len(pin_code)=4 then pin_code+'00'
when len(pin_code)=5 then pin_code+'0'
end
but its not working
brejk
SELECT RIGHT('000000' + MyVal, 6)
In your case:
1. If pin_code should be in the result set
select RIGHT('000000' + pin_code,6) from t_clnt_address
2. If pin_code should be used for sorting
select * from t_clnt_address order by RIGHT('000000' + pin_code,6)
1. If pin_code should be in the result set
select RIGHT('000000' + pin_code,6) from t_clnt_address
2. If pin_code should be used for sorting
select * from t_clnt_address order by RIGHT('000000' + pin_code,6)
select *, left( pin_code + '000000', 6)
from t_clnt_address
Ugh, sorry for my padding right instead of padding left ;-)
ASKER
Actually i want to update the all pin code whose length is less than 6 .
for that i want update statment with padding zero to right
for that i want update statment with padding zero to right
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks !!!!