February 10, 2011 at 5:06 am
Hi All,
I am trying to change a Decimal (18,2) to Char(12) which is simple enough however the figure needs to have spaces at the start instead of on the end. For example:
This...
20110209103.22 1
Needs to look like this...
20110209 103.221
Any ideas?
February 10, 2011 at 5:18 am
clarmatt73 (2/10/2011)
Hi All,I am trying to change a Decimal (18,2) to Char(12) which is simple enough however the figure needs to have spaces at the start instead of on the end. For example:
This...
20110209103.22 1
Needs to look like this...
20110209 103.221
Any ideas?
I'm sorry, but this doesn't make sense to me. It looks as if you're trying to render a date into a different format. Why do you have dates stored in a decimal column, and how does a decimal value have spaces in it? And, even if you don't count the spaces, your char(12) string has 15 characters in it. Please will you explain your requirement in more detail, with table DDL and sample data in the form of INSERT statements?
Thanks
John
February 10, 2011 at 6:35 am
Hi John,
Apologies for not being clear, the date is in the column beofre the decimal. I have been asked to create a file that goes into a fixed width import so when the data comes out in SSMS it looks like one long piece of text.
I already have the date so all i need to know is how do i get the decimal that will go next to it to have spaces in front of it.
February 10, 2011 at 6:47 am
Probably your best bet it to use bcp or SSIS to create your file. If you don't want to do that, please will you explain your requirement better? Do you want to pad your decimal with spaces at the beginning so that the right-hand endge of the column lines up? Or do you want the decimal points to line up? Please give an example of what the file will look like, with a few different values of your decimal?
John
February 10, 2011 at 7:44 am
Hi John,
The padding is exactly what i want to do, the report needs to look like the below.
1000.00
100.00
10.00
1.00
Therefore the 1000.00 has 5 spaces in front of it, 100.00 has 6 etc etc. The field needs to be set to a length of 12.
February 10, 2011 at 7:46 am
When i uploaded my reply it removed the spaces in front of the numbers in my example. Please see below new example with * for spaces.
*****1000.00
******100.00
*******10.00
********1.00
Thanks
February 10, 2011 at 7:57 am
Assuming your decimal field is called fieldname, you would do something like below.
SELECT REPLICATE(' ', 12 - LEN(CONVERT(VARCHAR(12),fieldname))) + CONVERT(VARCHAR(12),fieldname)
I haven't tested it, so the Convert syntax might be a bit off.
February 10, 2011 at 8:04 am
That worked perfectly, thanks for everyones replies.
February 10, 2011 at 8:14 am
Or something like this:
SELECT RIGHT(' '+CAST(MyDecimal as varchar(18),18)
But really, you should be using the right tool for the job. If you're producing a text file, use bcp or Integration Services.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply