Clever Geek Handbook

How to properly align a column when selecting data?

In a school assignment, I'm working on formatting the decimal (14.0) column "as currency [US $ xxx, xxx] and right justified [all commas are stacked vertically]".

I can select the data in the correct format using this:

CONCAT("US$", FORMAT(theColumn, 0))

But the data is wrong. I've searched and searched and just couldn't find a way to properly substantiate the conclusion.

I found an answer here that shows how to do this if the column is a string type and has a fixed width, but I cannot find a way to right-align the output for a decimal data type. Is it possible?

EDIT:

MySQL returns data to the left like this:

US$18,100,000,000
US$130,100,000,000
US$1,200,000,000

I want to select it correctly, for example:

 US$18,100,000,000
US$130,100,000,000
  US$1,200,000,000
+3


source to share


3 answers


I think the next query is the answer to your question. Since the maximum length for DECIMAL type (14,0), represented by separators, is 18, you need to figure out how many spaces to add before each concatenated result. I calculate the number of digits of a number, then the number of commas ((number length -1) DIV 3) and finally the number of spaces to add (18 - number length - separator length). Hope I don't miss anything.



SELECT column_name,
CONCAT("US$", FORMAT(column_name, 0)) as authorstry,
CHAR_LENGTH(CAST(FORMAT(column_name, 0) AS CHAR(18))) AS stringlength,
CHAR_LENGTH(column_name) as numofdigits, 
((CHAR_LENGTH(column_name)-1) DIV 3) as numofseparators,
(18-(CHAR_LENGTH(column_name))-((CHAR_LENGTH(column_name)-1) DIV 3)) as spacestobeadded
CONCAT(SPACE(18-(CHAR_LENGTH(column_name))-((CHAR_LENGTH(column_name)-1) DIV 3)) ,"US$",FORMAT(column_name, 0)) as finalresutl  
 FROM table;
-8


source


I think what you want

select lpad(column_name,x,' ') from table_name;


where x is the number of places you want to fill in this way (8 places so to speak)

+18


source


I would do:

LPAD(CONCAT("US$", FORMAT(theColumn, 0)),20,' ')

What it does is represent the output in a 20 character column, padding '' to the left of the values.

+5


source






More articles:


All Articles