I have a table with records like:

id   name
1    Rajesh mingol
2    jackish feryy jegtt
3    henry
4    utam
5      
6    yanky

I want to concatenate two columns like:

select concat(concat(name,'   '),id from table

It will come like this:

Rajesh mingol   1
jackish feryy jegtt   2
henry   3
utam   4
   5    
yanky   6

But I want the result like as follows:

Rajesh mingol         1
jackish feryy jegtt   2
henry                 3
utam                  4
                      5    
yanky                 6

Can you help me?

    Requires Free Membership to View

It appears from your use of the CONCAT function that you are using MySQL. We have to be careful here, because MySQL has many "proprietary" extensions to standard behaviour, and trimming trailing spaces is one of them. If your name column is defined as VARCHAR, and if you were to change it to CHAR with the intention of making all values have the same length, MySQL will "silently" trim the trailing spaces and store the values as VARCHAR anyway!

But never mind that. It's better that the names are stored as VARCHAR anyway. The trick to getting the result that you want is to manipulate the values when you SELECT them.

Let's assume your name column is defined as VARCHAR(25). First, concatenate a string of 25 spaces to the end of the name. Then take a substring of that result, such that you are extracting the leftmost 25 characters. Finally, concatenate the id to the result of the substring.

select concat(
          substring(
            concat(name,space(25))
              from 1 for 25
                   )
        , id ) as onefield
  from yourtable

Notice that the steps mentioned above (First... Then... Finally...) are actually evaluated "from the inside out" in the query. This is how nested functions work in all databases, not just MySQL.

This was first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: