0

I have a table members where I fetch values by concatenating FName and LName (as Full_Name) columns.

Here is my query: select CONCAT(FName,' ',LName) as Full_Name from members

The result will be like this

Full_Name
XXXXX XXXXX
XXXXXXXXXXX XXXXXXX
XXX XXXXXXXXXX

Is there any way to fetch data with a Fixed Width ? I tried with CAST function, but it won't aligns the results

Full_Name
XXXXX          XXXXX
XXXXXXXXXXX    XXXXXXX
XXX            XXXXXXXXXX
user3045457
  • 164
  • 1
  • 3
  • 12
  • 1
    The way you're displaying values should affect the way you fetch data in any way. Fix it where you display it, not in the query. That being said, it'd probably be better if you fetch the data separately and put it together in the view. – El_Vanja May 28 '21 at 09:00
  • Is there any special reason you want to put it in the database this way? – Michel May 28 '21 at 09:01
  • @Michel It is not for adding data, but only to show in a `select` box in HTML. If it can be formatted like my requirement in PHP, then also it is OK – user3045457 May 28 '21 at 09:04
  • @El_Vanja If it can be formatted like my requirement in PHP, then also it is OK – user3045457 May 28 '21 at 09:06
  • 1
    Even if you fill with blanks, you have to use a monospaced font to align them propperly. See [this question](https://stackoverflow.com/q/25879753/1685196) – Michel May 28 '21 at 09:23

3 Answers3

1

You can do it with a query. You have to find the longest FName and then RPAD to that lenght:

SELECT 
  CONCAT( RPAD(FName, alen + 5," " ), Lname ) AS Full_Name 
FROM(
  SELECT m.Fname, m.Lname, q.alen FROM members m
    JOIN(
    SELECT MAX(LENGTH(Fname)) AS alen FROM members
    ) q
) m

Fiddle

Michel
  • 4,076
  • 4
  • 34
  • 52
  • 1
    what is flen here ? It will be an undefine column. – John Doe May 28 '21 at 09:30
  • @Typo, tested it in my own db with different names – Michel May 28 '21 at 09:30
  • Its not working as according to the OP requirement. There are some spaces issues in it.\ – John Doe May 28 '21 at 09:33
  • @JohnDoe Does `CONCAT` remove extra spaces? When I use a dot it works prefect (see fiddle), with spaces not. – Michel May 28 '21 at 09:39
  • @Michel It works with a dot. But blank space or `&nbsp` not working. I will continue with a dot (or any other character) for time being. Thanks anyway – user3045457 May 28 '21 at 09:44
  • Its working with dot but not with spaces man. – John Doe May 28 '21 at 09:45
  • @user3045457 Weird, in my own db (Server version: 10.1.29-MariaDB) it works like a charm with spaces too. – Michel May 28 '21 at 09:48
  • @Michel in my phpMyAdmin, Space works fine. But I put the result in `select` box in html file, spaces disappears. :) You know the reason :) So I end-up with a dot for time being – user3045457 May 28 '21 at 09:56
  • @user3045457 I got it working using `CHAR(0xc2a0)` (= UTF-8 non breaking space) instead of `" "`. Still needs a monospaced font to align propperly. – Michel May 28 '21 at 10:57
0

I think the representation login should be done in code. So I post below PHP code solution:

<?php
// Find the longest name
$query = "SELECT MAX(LENGTH(FName)) as max_name from members;";
$stmt = $pdo->prepare($query);
$stmt->execute();
$max_name_length = $stmt->fetchColumn();

// Fetch table data
$query = "SELECT FName, LName from members;";
$stmt = $pdo->prepare($query);
$stmt->execute();
$members = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Build data representation
foreach($members as $member) {
    echo str_pad($member['FName'], $max_name_length + 2) . $member['LName'] . PHP_EOL;
}

Execute PHP code online

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
-1

Use the right padding function with the first name and then concat them.

Omkar Arora
  • 158
  • 1
  • 15