| Author |
Topic |
|
jbrandes99
Starting Member
5 Posts |
 Posted - 2011-06-29 : 10:11:49
|
| I am trying to figure out a way to create a statement that displays {account_no}+{invoice_total}. The problem I am having is that the results need to be 19 characters, with zeros filling in any characters in the middle. For example: 1234567000000234567Any ideas? |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-06-29 : 10:17:52
|
| declare @str1 varchar(20)declare @str2 varchar(20) SELECT @str1 = '1234567',@str2 = '234567'SELECT @str1 +replicate('0',19-len(@str1+@str2))+@str2JimEveryday I learn something that somebody else already knew |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-29 : 10:19:03
|
What does that mean?Has account_no always the length of 7?Has invoice_total always to become length of 12?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jbrandes99
Starting Member
5 Posts |
Posted - 2011-06-29 : 10:23:21
|
| The length of either field can change, the only constant is the maximum total characters of 19. |
|
|
|
jbrandes99
Starting Member
5 Posts |
Posted - 2011-06-29 : 10:24:49
|
quote:
Originally posted by jimf
declare @str1 varchar(20)declare @str2 varchar(20) SELECT @str1 = '1234567',@str2 = '234567'SELECT @str1 +replicate('0',19-len(@str1+@str2))+@str2JimEveryday I learn something that somebody else already knew
would @str1=account_no and @str2=invoice_total? |
|
|
|
jbrandes99
Starting Member
5 Posts |
Posted - 2011-06-29 : 10:25:17
|
quote:
Originally posted by webfred
What does that mean?Has account_no always the length of 7?Has invoice_total always to become length of 12?
No, you're never too old to Yak'n'Roll if you're too young to die.
The length of either field can change, the only constant is the maximum total characters of 19. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-29 : 10:25:38
|
| declare @str1 varchar(20)declare @str2 varchar(20) SELECT @str1 = '1234567',@str2 = '234567'select RIGHT(replicate('0',7)+@str1,7) + RIGHT(replicate('0',12)+@str2,12)That left pads both with zeroes. To right padleft(@str1+replicate('0',7),7)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-29 : 10:27:18
|
quote:
Originally posted by jbrandes99
The length of either field can change, the only constant is the maximum total characters of 19.
So you want leading zero's if the length of account_no is less than 7 at the beginning too and not only in the middle?edit: so see nigel's solution 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jbrandes99
Starting Member
5 Posts |
Posted - 2011-06-29 : 10:41:46
|
quote:
Originally posted by webfred
quote:
Originally posted by jbrandes99
The length of either field can change, the only constant is the maximum total characters of 19.
So you want leading zero's if the length of account_no is less than 7 at the beginning too and not only in the middle?edit: so see nigel's solution
No, you're never too old to Yak'n'Roll if you're too young to die.
leading zeros will only be in the middle. The account_no will always be on the left, followed by zero's and then ending with the invoice_total. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-06-29 : 10:44:26
|
Then you should be satisfied with jimf's post and yes @str1=account_no and @str2=invoice_total
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-29 : 10:56:59
|
| declare @str1 varchar(20)declare @str2 varchar(20) SELECT @str1 = '1234567',@str2 = '234567'select left(@str1+replicate('0',7),7) + RIGHT(replicate('0',12)+@str2,12)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Pad with Zeros