Problem solve Get help with specific problems with your technologies, process and projects.

Nested functions

I have a table with records like:

id   name
1    Rajesh mingol
2    jackish feryy jegtt
3    henry
4    utam
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
yanky   6

But I want the result like as follows:

Rajesh mingol         1
jackish feryy jegtt   2
henry                 3
utam                  4
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(
              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.

Dig Deeper on Oracle and SQL