Q

Nested functions

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close