February 28, 2006 at 6:00 am
Hi,
I have 3 columns in my database table
a) Amount
b) CustName
c) ConfirmDate
I want to retrieve all records from this table for all 3 columns but with a fixed size(since I have to write it to a text file)
example I want the Amount column to be 13 characters and if the value in the table is 1000 then it should be appended by zeros on the left example 0000000001000
and the CustName should be retrieved as 10 characters(empty spaces appended to the right... example if 'Mandy' then 'Mandy ' and the ConfirmDate should be retrieved as DDMMYYYY
Can I do this in a sql query. Thanks...
February 28, 2006 at 6:58 am
SELECT
CAST(CustName AS CHAR(10)),
REPLICATE('0', 13 - LEN(CAST(CAST(Amount AS INT) AS VARCHAR))) + CAST(CAST(Amount AS INT) AS VARCHAR),
REPLACE(CONVERT(CHAR(10), ConfirmDate, 103), '/','')
FROM table1
You may have to be careful with the customer name because some clients may trim the trailing spaces so if the order of fields is the same you described you may want to concat them in SQL.
SELECT
CAST(CustName AS CHAR(10)) +
REPLICATE('0', 13 - LEN(CAST(CAST(Amount AS INT) AS VARCHAR))) + CAST(CAST(Amount AS INT) AS VARCHAR) +
REPLACE(CONVERT(CHAR(10), ConfirmDate, 103), '/','')
FROM table1
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 28, 2006 at 7:01 am
PS.
I casted the Amount to INT, because your post kind of indicated this. If you want the cents included you have to specify how do you want them displayed. with or without the decimal point.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 28, 2006 at 7:23 am
Could use if int (if not int then STR will round)
REPLACE(STR(Amount,13,0),' ','0')
or with decimal point
REPLACE(STR(Amount,13,2),' ','0')
or without decimal point but including decimal part
STUFF(REPLACE(STR(Amount,14,2),' ','0'),12,1,'')
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply