[SOLVED] How to right-align a column when selecting data?

Issue

A school assignment I’m working on says to format a decimal(14,0) column “as currency [US$xxx,xxx] and right justified [all the commas line up vertically].”

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

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

But the data is not right justified. I’ve been searching and searching and simply haven’t been able to find any way to right justify the output.

I did find an answer on here that shows how to do it if the column is a string type and has a fixed width, but I can’t find a way to right justify the output for a decimal data type. Is it possible?

EDIT:

MySQL returns data left justified, like this:

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

I want to select it right justified, like this:

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

Solution

I think the following query is the answer to your question.
Since the maximum length for a DECIMAL(14,0) type as represented with separators is 18 you have to find out how many spaces you need to add in front of every concatenated result. I calculate the number of digits of number, then the number of commas ((length of the number -1) DIV 3) and finally the number of spaces to be added (18 – numberlength – separatorslength).Hope I am not missing something.

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;

Answered By – geoandri

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)