|
View previous topic :: View next topic
|
| Author |
Message |
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
Hi all,
i need to convert a field of a table from char to number with a SELECT statement.
Input field (char 20):
| Code: |
'7 '
'24 '
'155 '
'3876 '
'74567 ' |
Output field
| Code: |
00007
00024
00155
03876
74567 |
I need ONLY the first five digits.
Thank you |
|
| Back to top |
|
 |
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
I have found this:
| Code: |
| SELECT RIGHT(REPEAT('0', 5) || RTRIM(CHAR(table_field)), 5) |
it seems to work but only if the input field (CHAR) there are numbers, otherwise ..... |
|
| Back to top |
|
|
danylele74
New User
Joined: 03 Jul 2014
Posts: 28
Location: Italy
|
|
|
|
| The maximun output length is 5 (five) digits. |
|
| Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
If you are sure that you have only numbers and spaces in this column, you can try:
| Code: |
| INT(YOUR_CHAR_COLUMN) |
(Instead of INT you can use BIGINT or DEC).
In all cases, there are restrictions:
| Quote: |
The string must contain a valid string representation of a number.
Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an integer constant.
|
Once it works, try:
| Code: |
| DIGITS(INT(YOUR_CHAR_COLUMN)) |
and then
| Code: |
| SUBSTR(DIGITS(INT(YOUR_CHAR_COLUMN)),6,5) |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
Also, this should give you what you want.
| Code: |
select
LPAD(replace('7 ',' ',''),5,'0')
from sysibm.sys1dummy |
|
|
| Back to top |
|
|
|
|
