| Author |
Message
|
| Sridar |
 Posted: Mon Jun 12, 2006 5:13 am Post subject: [Solved] Padding using OVERLAY function |
 |
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi
Am having a small pblm in using Overlay function.
This is what i am trying to do:
For certain number fields whose length is less than the required length i append a '0' in the beginning.
e.g. 19 is to be converted to 019 and 9 is to be converted to 009.
The length also varies depending on the field.
I am using the code below.
When i specify the start position as 1 the following is the result
19 is converted to 09 instead of 019
instead if i give the start position as 0 the following is the result
19 is converted to 19019.
Please advise.
CREATE FUNCTION CheckItemValue(ITEM_VALUE CHARACTER, ITEM_LENGTH INTEGER) RETURNS CHARACTER
BEGIN
DECLARE LENGTH INTEGER;
IF(LENGTH(ITEM_VALUE) < ITEM_LENGTH) THEN
SET LENGTH = ITEM_LENGTH - LENGTH(ITEM_VALUE);
SET ITEM_VALUE= OVERLAY(ITEM_VALUE PLACING '0' FROM 1 FOR LENGTH);
RETURN ITEM_VALUE;
ELSE
RETURN ITEM_VALUE;
END IF;
END;[/code]
_________________
Thanks and Regards
Sridar
Last edited by Sridar on Mon Jun 12, 2006 11:47 pm; edited 1 time in total |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Mon Jun 12, 2006 5:26 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Sridar,
Have u looked at the LEFT function.
Regards. |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Jun 12, 2006 5:32 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi elvis
Isn't the LEFT function for truncating.
I want 0 to be appended to increase the length of the string.
i.e. if the value of the field is 19 i want to make it into 019
Sridar
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jun 12, 2006 5:53 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Sridar,
I meant, do something like this. Not tested..
| Code: |
-- declare a string with the max length u suppose an element might be having.
DECLARE concated CHAR '00000000000000000';
SET ITEM_VALUE = LEFT(concated || ITEM_VALUE, ITEM_LENGTH);
RETURN ITEM_VALUE; |
Regards. |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Jun 12, 2006 6:01 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Thanks elvis
Sorry i misunderstood your suggestion. I think this may work,
But actually i am trying to use a common function which will cater to multiple fields.
Each field have different length so in this case i need to create a variable for each different field that i will be passing to the function.
Is there a way for doing this.
Is anything wrong in my code
Thanks
Sridar
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jun 12, 2006 6:25 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Sridar,
No need for a sorry, when u first questioned the suggestion, I myself could not remember why i even suggested it 
I agree if you get fields of different lengths, then it will be a problem...thats why i said, make the length the longest that u think a field could get.
Anyway this function is good if you are appending spaces...there u can use SPACE(ITEM_LENGTH)...
Now to your current problem...you are using OVERLAY, but using it wrongly...when u do a
| Code: |
| OVERLAY(ITEM_VALUE PLACING '0' FROM 1 FOR LENGTH); |
0 will be put OVER the ITEM_VALUE from the position 1 to the length specified...
You can rather use a while loop and concat '0' infront of ITEM_VALUE until the ITEM_LENGTH is reached.
Regards. |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Jun 12, 2006 6:43 am Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Thanks elvis
That was my next step if this OVERLAY was not solved.
I thought of using a single statement instaed of a loop,
but as you say if this won't work then the loop is the only possibility.
Will try and let you know
Sridar
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Jun 12, 2006 11:03 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi elvis,
Instead of using a loop i used the REPLICATE function
this is what i coded
CREATE FUNCTION CheckItemValue(ITEM_VALUE CHARACTER, ITEM_LENGTH INTEGER) RETURNS CHARACTER
BEGIN
DECLARE STR_LENGTH INTEGER;
SET STR_LENGTH = ITEM_LENGTH - LENGTH(ITEM_VALUE);
IF(LENGTH(ITEM_VALUE) < ITEM_LENGTH) THEN
SET ITEM_VALUE = REPLICATE('0', STR_LENGTH) || ITEM_VALUE;
RETURN ITEM_VALUE;
ELSE
RETURN ITEM_VALUE;
END IF;
END;
Sridar
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jun 12, 2006 11:16 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Sridar,
Looks cool. congrats
Please mark the topic solved too...will be a good reference for the future.
Regards. |
|
| Back to top |
|
|
| Sridar |
| Posted: Mon Jun 12, 2006 11:26 pm Post subject: |
|
|
Acolyte
Joined: 14 May 2006
Posts: 72
Location: Chennai, India
|
Hi elvis,
Am new to mqseries.net
Can you tell me how to mark the topic solved?
Thanks
Sridar
_________________
Thanks and Regards
Sridar |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Mon Jun 12, 2006 11:40 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Sridar,
U should see an edit button in the first post u made for this topic....
click that to edit your first post....
in the Subject line add a [Solved] before the subject of the topic..
Regards. |
|
| Back to top |
|
|
|
|
