How to align a column right-adjusted

Question

I want to align a column in my table. In this case 'Title'

mysql> SELECT Titel, KuenstlerName from Bild;
+--------------------------+---------------+
| Title                    |         Artist|
+--------------------------+---------------+
| Drei Musikanten          | Picasso       |
| Buveuse assoupie         | Picasso       |
| Die Flöte des Pan        | Picasso       |
| Paolo als Harlekin       | Picasso       |
| Die Umarmung             | Picasso       |
| Sitzende Frau            | Picasso       |
| Sternennacht             | van Gogh      |
| Der Park                 | Klingt        |
| Liegender Hund im Schnee | Marc          |
| Hauptweg und Nebenwege   | Klee          |
| Jungfrau im Baum         | Klee          |
| Das gelbe Haus           | Klee          |
+--------------------------+---------------+

Is it possible to align the left column to the right, like this:

+--------------------------+---------------+
| Title                    | Artist        |
+--------------------------+---------------+
|           Drei Musikanten| Picasso       |
|          Buveuse assoupie| Picasso       |
|         Die Flöte des Pan| Picasso       |
+--------------------------+---------------+

Thanks for your help!

Awash

sql is a database. It handles data, it is not for handling the display of it. Yes, you can pad your string as seen below, but that changes the data (adds spaces) and not it's display. It also will only work in fixed-font scenarios. – Divertimento

Answer 1

If your Title length is 150 then query like this

 SELECT LPAD(Titel,150,' ') as Titel , KuenstlerName from Bild;

Check Manual for LPAD function.

Anthropomorphous

Don't worry for that you accepted is more important for other user for future reference. – Anthropomorphous

Answer 2

If Titel is 100 characters, then do this

SELECT
    LPAD(Titel, 100, ' '),
    ...

or

SELECT
    RIGHT(CONCAT(REPEAT(' ', 100), Titel), 100),
    ...

Christianly

The second option worked for SQL on iSeries DB2. My need wasn't for right adjusting the field, but rather an input variable going into a query. Worked Wonderfully. Allowed the optimizer to use the correct index. – Tiddly

Answer 3

I solved the task like this:

SELECT LPAD(Titel,(SELECT MAX(LENGTH(Titel)) FROM Bild),' ') AS Titel, CONCAT(Kuenstler.Vorname,' ',Kuenstler.Name) AS Kuenstler 
FROM Kuenstler 
INNER JOIN Bild ON Bild.Kuenstler = Kuenstler.KID;

Cymbal

Answer 4

I've found the easiest way is to use the 'CAST' function.

Say my select reads:

SELECT AMTDUE FROM INVOICES;

That return a left aligned string of values.

Changing the statement to:

SELECT CAST(AMTDUE AS DECIMAL(10,2)) AS AmtDue FROM INVOICES;

Now I have a right justified column of numbers.

Trichina

There are no numbers in the table. You can't cast a title to decimal. – Pietra