Joined: 21 Nov 2005 Posts: 31 Topics: 10 Location: Orange County, California
Posted: Thu Dec 11, 2008 5:11 pm Post subject: How to limit an output field size from concatenated fields
I am trying to concatenate 3 columns in an SQL statement. The first column is an integer, but I only want the 4 low order digits of the column. The second column is decimal, the third is a name (varchar). Also the maximum length of the output field generated must be 25 characters or less.
What I'm trying to arrive at is this:
Input
column 1 = 000012345
column 2 = 19500101
column 3 = abcedfghijklmnop (this column can be 40 characters max)
desired output is:
234519500101abcdefghijklm
I am creating a '|' DELIMITED output file and need to do all the formatting within my SQL statement.
The code with the '--->' is the one in question. I know it's not the correct way to code the statement. That field plus the others can not exceed 25 bytes, and it can not have any trailing spaces.
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu Dec 11, 2008 5:40 pm Post subject: Re: How to limit an output field size from concatenated fiel
pcmoonbeam wrote:
I am trying to concatenate 3 columns in an SQL statement. The first column is an integer, but I only want the 4 low order digits of the column. The second column is decimal, the third is a name (varchar). Also the maximum length of the output field generated must be 25 characters or less.
What I'm trying to arrive at is this:
Input
column 1 = 000012345
column 2 = 19500101
column 3 = abcedfghijklmnop (this column can be 40 characters max)
desired output is:
234519500101abcdefghijklm
RTRIM by itself in that concatenated field might not give the desired result if the first name exceeds 13 bytes.
Any Ideas?
Unless I am missing something isnt it this simple? I assumed that the max decimal column defintion is 8 bytes.
Joined: 21 Nov 2005 Posts: 31 Topics: 10 Location: Orange County, California
Posted: Thu Dec 11, 2008 5:52 pm Post subject:
I'm not sure if you understood the problem Kolusu.
The decimal column is not really the issue. It's the var_char COL which is actually a first name. It can be up to 40 bytes, but the combined length of the 3 columns can not exceed 25 characters and there can be no trailing spaces.
Examples:
Code:
1) wrong format would be
"|123419801030JAMES |"
2) correct foramt would be
Code:
"|123419801030JAMES|"
The differece between the two examples is that the first one has trailing spaces after the name James, whereras the second example doesn't have trailing spaces.
The PIPE character '|' is the delimiter of the concatenated field. _________________ Thanks,
PCMOONBEAM
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Thu Dec 11, 2008 6:29 pm Post subject:
pcmoonbeam wrote:
But still the problem is the character field which could be up to 40 bytes. I hope my example was clear.
4 bytes of integer column + 8 bytes of decimal column = 12 bytes. so you always pick the first 13 bytes of the varchar column and use RTRIM On the 13 bytes removing the trailing spaces resulting in 25 byte output in total
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
