Output padded result
in [Programming SQL]
|
Prev: The column prefix 'c' does not match with a tablename or alias used in the query
Next: How do I search an entire table for a given values? - Please help with the sql
From: DavidC on 5 Aug 2010 11:52 Is there a way to "pad" defined characters when using SELECT on numeric or varchar data? For example SELECT TranAmount, City FROM TranDetail If the TranAmount were smallmoney and rounded to 1234.50 for example, I would like to remove the decimal point and pad with leading zeros up to a specific size. So if I needed 10 characters it would output 0000123450 For a varchar column with City = Chicago and needed 20 characters it would output 'Chicagoxxxxxxxxxxxxx' where x's are spaces. Thanks. -- David
From: Plamen Ratchev on 5 Aug 2010 12:02 Here is one method: SELECT REPLACE(REPLACE(STR(TranAmount, 11, 2), '.', ''), ' ', '0') AS transaction_amount, CAST(City AS CHAR(20)) AS city FROM TranDetail; -- Plamen Ratchev http://www.SQLStudio.com
From: DavidC on 5 Aug 2010 12:50 I will give those a try. Thanks. -- David "Plamen Ratchev" wrote: > Here is one method: > > SELECT REPLACE(REPLACE(STR(TranAmount, 11, 2), '.', ''), ' ', '0') AS > transaction_amount, > CAST(City AS CHAR(20)) AS city > FROM TranDetail; > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
From: --CELKO-- on 5 Aug 2010 23:12 Why are you formatting data in the back end? The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This is a more basic programming principle than just SQL and RDBMS. We did this 50 years ago in COBOL and Autocoder when we had the app and the data fued into a monolithic system. Why are you coding like this today?
From: DavidC on 6 Aug 2010 06:57
Because I want to dump the data and not impose a row-by-row read of the dataset. -- David "--CELKO--" wrote: > Why are you formatting data in the back end? The basic principle of a > tiered architecture is that display is done in the front end and never > in the back end. This is a more basic programming principle than just > SQL and RDBMS. > > We did this 50 years ago in COBOL and Autocoder when we had the app > and the data fued into a monolithic system. Why are you coding like > this today? > . > |