| View previous topic :: View next topic |
| Author |
Message |
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
 Posted: Thu Jul 06, 2006 8:31 am Post subject: Help Regarding SUBSTR function... |
 |
|
hi all,
i need help regarding SUBSTR function.
i want to select a column in a db2 table which is Decimal(11,0) in the way
that last 2 characters to be at first and 1st 9 characters concatenated with that.
i tried SUBSTR(char(var1),9,2) concat SUBSTR(char(var1),1,9), but it is not giving the expected result!
Please help me out in this!
_________________
Satheesh |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 06, 2006 8:40 am Post subject: |
|
|
satheeshs,
Is this DB2 question or a REXX question ? If it is a DB2 question it must be posted in the Database forum. | Quote: |
that last 2 characters to be at first and 1st 9 characters concatenated with that.
i tried SUBSTR(char(var1),9,2) concat SUBSTR(char(var1),1,9), but it is not giving the expected result!
|
A column defined as decimal (11,0) is 11 bytes long and if you want the last 2 digits in the beginning then you should start from 10th byte. You are starting from 9th byte for length of 2 which would take 9 and 10th byte, which is the reason for your incorrect results.
try this
| Code: | SUBSTR(char(var1),10,2) concat
SUBSTR(char(var1),01,9) |
Hope this helps...
Cheers
Kolusu
Ps: Please clarify if this question is indeed a Db2 Question , so that I can move it to the approriate forum.
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
| Posted: Thu Jul 06, 2006 8:50 am Post subject: |
|
|
Thanks all,
i apologize for posted this question in this forum. i am very new to this forum.
i tried with SUBSTR(char(var1),10,2) concat SUBSTR(char(var1),01,9)
but still is not giving the expected result
what i have tried :
47302899954 is the value in the table of column which is of type DECIMAL(11,0)
When i have given the query SELECT SUBSTR(char(var1),10,2) concat SUBSTR(char(var1),01,9) from x.table;
it had given me
95 47302899
(with the space after the '95')
P.S:if possible move it to DB" forum. Thanks . Sorry again!
_________________
Satheesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 06, 2006 8:56 am Post subject: |
|
|
| Quote: |
When i have given the query SELECT SUBSTR(char(var1),10,2) concat SUBSTR(char(var1),01,9) from x.table;
it had given me
95 47302899
(with the space after the '95')
|
satheeshs,
That is only Spufi's Display putting a space between them. Try unloading the data using the same query and you will not see the space in between.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
| Posted: Thu Jul 06, 2006 9:03 am Post subject: |
|
|
putting a space in between is fine, but a number got truncated.
The expected result should be
54 473028999
but it is coming as
95 47302899
that's why i feel doubt about the space between the numbers
_________________
Satheesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 06, 2006 9:16 am Post subject: |
|
|
| Quote: |
putting a space in between is fine, but a number got truncated.
|
That is because the CHAR function reserves the leading byte for Sign. It puts a space for the positive numbers and '-' for negative numbers. If you don't have any negative numbers then you can use DIGITS function instead of CHAR function.
| Code: |
SUBSTR(DIGITS(ID_NO),10,2) ||
SUBSTR(DIGITS(ID_NO),01,9)
|
How do you wanna handle negative numbers?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
| Posted: Thu Jul 06, 2006 9:23 am Post subject: |
|
|
Thanks kolusu.
it works. in my table, it has only positive numbers. so no probs.
if possible could you tell me to handle negative numbers, i could learn!
_________________
Satheesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 06, 2006 9:36 am Post subject: |
|
|
| Quote: |
if possible could you tell me to handle negative numbers, i could learn!
|
satheeshs,
Where do you want the negative sign ?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
| Posted: Thu Jul 06, 2006 9:43 am Post subject: |
|
|
in the first plaec itself.
say -4578
should come as
-7845
_________________
Satheesh |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 06, 2006 9:45 am Post subject: |
|
|
satheeshs,
Try this
| Code: |
SELECT CASE WHEN Dec_Col < 0 THEN
CHAR('-') ||
SUBSTR(DIGITS(Dec_Col),10,2) ||
SUBSTR(DIGITS(Dec_Col),01,9)
ELSE
CHAR('+') ||
SUBSTR(DIGITS(Dec_Col),10,2) ||
SUBSTR(DIGITS(Dec_Col),01,9)
END
FROM TABLE
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
satheeshs
Beginner
Joined: 06 Jul 2006
Posts: 13
Topics: 3
Location: 'Mainframe World'
|
| Posted: Thu Jul 06, 2006 9:48 am Post subject: |
|
|
Thanks kolusu
_________________
Satheesh |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
