| View previous topic :: View next topic |
| Author |
Message |
showkath
Beginner
Joined: 23 Mar 2005
Posts: 17
Topics: 7
|
 Posted: Tue Jun 07, 2005 5:58 am Post subject: RPAD / LPAD function in DB2 |
 |
|
Hi,
Could anyone please let me know whether
DB2 has RPAD / LPAD function similar to Oracle.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 07, 2005 8:11 am Post subject: |
|
|
Showkath,
As far as I know DB2 does not have direct function which relates to the LPAD/RPAD (left/right padding of a specific character) function of oracle.
However there are many ways to achieve the desired results. Simplest being using Concatenation of the desired string.
Please show us an example of the data and desired output along with the column definitions.
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
schintala
Beginner
Joined: 18 May 2005
Posts: 108
Topics: 31
Location: USA
|
| Posted: Tue Jun 07, 2005 2:10 pm Post subject: |
|
|
| DB2 doesn't have the functions LPAD/RPAD which are on Oracle. Alternatively can't you make use of LTRIM/RTRIM? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 07, 2005 3:03 pm Post subject: |
|
|
| Quote: |
Alternatively can't you make use of LTRIM/RTRIM?
|
Schintala,
LTRIM/RTRIM are the exact opposites of LPAD/RPAD function of oracle.
In Oracle, the LPAD function pads the left-side of a string with a specific set of characters where as the LTRIM function removes blanks from the beginning of a string expression.
In Oracle, the RPAD function pads the right-side of a string with a specific set of characters where as the RTRIM function removes blanks from the end of a string expression.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
showkath
Beginner
Joined: 23 Mar 2005
Posts: 17
Topics: 7
|
| Posted: Tue Jun 07, 2005 10:29 pm Post subject: Actual requirement... |
|
|
Kolusu,
Thanks for your timely reply.
Let us consider the following example.
A table TMP001 has a column MAIL_ID of type char(10) which
would have values in the form COMP001, COMP002, COMP003.......
The requirement is to
i) select the maximum value or the latest MAIL_ID
and show the next sequence. If maximum value is COMP003
then the select should return COMP004 and;
ii) if no row exists
it should return COMP0001.
For the above, I have written the following query
SELECT DISTINCT COALESCE(
SUBSTR(MAIL_ID,1,4) || SUBSTR(STRIP(CHAR(XX.VAL1)),3,4),
'COMP0001')
FROM TMP001, TABLE
(SELECT MAX(CAST(SUBSTR(MAIL_ID,5) AS DEC)+1) AS VAL1 FROM TMP001) XX
This query works fine for the requirement no i). But for Req ii) query does
not returns anything.
Another problem is, while converting to DEC and concatenation
extra zeroes are padded towards the left part. For this again
I have to use substr.. 3,4 to extract the required part. So I
thought that If I use INT() function while generating the next seq
, it would return only integer part. If LPAD fn. is used for req.
No. of zeroes, then process would be simple.
Sorry for lengthy reply.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 08, 2005 5:34 am Post subject: |
|
|
| Quote: |
Another problem is, while converting to DEC and concatenation
extra zeroes are padded towards the left part. For this again
I have to use substr.. 3,4 to extract the required part.
|
Showkath,
Why are picking only 4 bytes? what happens once the counter reaches 10,000? Would start over once again?
Ideally I would think of COMP followed 6 digit numeric field which would go up to atleast 999,999.
ie. COMP000001
Fetching a value of 1 is very easy as you can add UNION clause and another select to get 1 as the seqnum and order it by the seqnum desc so that you have the max as the first row and use FETCH FIRST 1 row only which gives you only 1 row at a time.
EX:
| Code: |
SELECT COALESCE(CHAR('COMP') CONCAT
A.MAX_VAL,CHAR('COMP000001'))
FROM (
SELECT DIGITS(MAX(DEC(REPLACE(MAIL_ID,'COMP','0'),5)) + 1) MAX_VAL
FROM TABLE
UNION
SELECT DIGITS(DEC('1',6)) MAX_VAL
FROM SYSIBM.SYSDUMMY1) A
ORDER BY 1 DESC
FETCH FIRST 1 ROW ONLY
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
showkath
Beginner
Joined: 23 Mar 2005
Posts: 17
Topics: 7
|
| Posted: Wed Jun 08, 2005 6:07 am Post subject: |
|
|
Kolusu,
Thanks for your reply.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
