Field value to the length of the column
Moderators: chulett, rschirm, roy
Field value to the length of the column
Hi,
I have a query while creating a fixed width file
1. If there is an integer field(or even a char type) of length 6 how do I get 0s(or another padding character) appended to the beginning
Ex:
Input : 1234
Expected Output : 001234(Length of the target column is 6 )
I have tried the options
a) Str(0,6-Len(column)):column
b) Right('000000':column,6)
In this option I understand that if the length of the column is more for example 30, it would be required to use 30 0s to
concatenate where human errors can creep in
Both the option suffice my expected result but there are many such columns and required to hard code. I would like to know if it is possible without hard coding.
If at all it is required to hard code, whcih of the above options would be suggested in terms of performance.
My input data volume varies from 1000s of records to millions (max 5 million) of records
Please suggest which of the above options is better in terms of constrcution and also performance
I have a query while creating a fixed width file
1. If there is an integer field(or even a char type) of length 6 how do I get 0s(or another padding character) appended to the beginning
Ex:
Input : 1234
Expected Output : 001234(Length of the target column is 6 )
I have tried the options
a) Str(0,6-Len(column)):column
b) Right('000000':column,6)
In this option I understand that if the length of the column is more for example 30, it would be required to use 30 0s to
concatenate where human errors can creep in
Both the option suffice my expected result but there are many such columns and required to hard code. I would like to know if it is possible without hard coding.
If at all it is required to hard code, whcih of the above options would be suggested in terms of performance.
My input data volume varies from 1000s of records to millions (max 5 million) of records
Please suggest which of the above options is better in terms of constrcution and also performance
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
B is more efficient. This should only be an issue with string fields, for numeric values the 'padding' with zeroes should be happening automatically. Are you not seeing that?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Craig,
Decimal datatypes auto pad with zeros while integers do not.
Yes, if defined as Decimal/Numeric, it does have the decimal point which is not preferred along with a space for the sign
And in case of strings, padding by default is to the right as defined by the $APT_STRING_PADCHAR
I am not aware of if there is an option to define left padding
Hi Eric,
In terms of construction effort I agree that option B is easier but what in terms of performance
Decimal datatypes auto pad with zeros while integers do not.
Yes, if defined as Decimal/Numeric, it does have the decimal point which is not preferred along with a space for the sign
And in case of strings, padding by default is to the right as defined by the $APT_STRING_PADCHAR
I am not aware of if there is an option to define left padding
Hi Eric,
In terms of construction effort I agree that option B is easier but what in terms of performance
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
What I said above was option B does less work (meaning better performance). You said it was easier, and I would agree with that too.harikhk wrote:In terms of construction effort I agree that option B is easier but what in terms of performance
Choose a job you love, and you will never have to work a day in your life. - Confucius
Craig,
Overlooked the link. Thanks for sharing the part.
For performance noted option B better
Still intersted to know if there is another option to avoid hard coding or is only the option considering below scenario
Just in case the target field is a 200 character length and has 50 input characters, is it that the only option as we have to mention 200 zeros(or a padding character for string fields) and then concatenate and apply the right function (as left padding is what I am interseted in)?
And one more query which is a bit connected(hope so), how do write a null field value to the length of the field
Ex: For a field of length 6
Input : NULL
Output : ' ' (the quotes here are only for display and the actual output is 6 spaces)
There could be multiple such null value fields.
Should I go with option b after checking if input value is null .
Overlooked the link. Thanks for sharing the part.
For performance noted option B better
Still intersted to know if there is another option to avoid hard coding or is only the option considering below scenario
Just in case the target field is a 200 character length and has 50 input characters, is it that the only option as we have to mention 200 zeros(or a padding character for string fields) and then concatenate and apply the right function (as left padding is what I am interseted in)?
And one more query which is a bit connected(hope so), how do write a null field value to the length of the field
Ex: For a field of length 6
Input : NULL
Output : ' ' (the quotes here are only for display and the actual output is 6 spaces)
There could be multiple such null value fields.
Should I go with option b after checking if input value is null .
Last edited by harikhk on Thu May 22, 2014 2:29 pm, edited 1 time in total.
Thanks,
HK
*Go GREEN..Save Earth*
HK
*Go GREEN..Save Earth*
An actual NULL field would be just two quotes with nothing between them. If you want a string of equivalent size filled with spaces for your fixed-width file, there is a Spaces() function from what I recall so Spaces(6) would work for your example. Or a CHAR field with the PAD_CHAR set to 0x20 should work, too.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Instead of hard-coding 200 zeros, you could go for something in-between options A and B. I would not expect any of the options to "kill" the performance. You could also move the Str() function into a stage variable and reuse it across the variety of derivations. The tradeoff here would be reducing potential for human errors for a bit of performance (may be difficult to measure).harikhk wrote:Just in case the target field is a 200 character length and has 50 input characters, is it that the only option as we have to mention 200 zeros(or a padding character for string fields) and then concatenate and apply the right function (as left padding is what I am interseted in)?
Option C
Code: Select all
Right(Str('0', 200):column, 200)
Choose a job you love, and you will never have to work a day in your life. - Confucius
I was specifically responding to a scenario where the field was only populated with pad characters - your NULL string. In that case left vs. right makes no difference.harikhk wrote:PAD_CHAR helps for right padding. I am looking for a left padding option
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers