|
View previous topic :: View next topic
|
| Author |
Message |
Amolondhe007
Warnings : 1
New User
Joined: 16 Apr 2007
Posts: 49
Location: Pune
|
|
|
|
Hi,
Supposed i have one Table - Emplyoee
It has two Columns- First Name and Last Name.
And See Table Format as below:
| Code: |
First name Last Name
Amol asa
Vishal dfgg
Manoj dgsdf
Ajay dfg
Nitin sfasf |
Now I want the Ouput as:
| Code: |
Amol asa
Vishal dfgg
Manoj dgsdf
Ajay dfg
Nitin sfasf
|
What will be the SQL query for this? |
|
| Back to top |
|
 |
PeD
Active User
Joined: 26 Nov 2005
Posts: 459
Location: Belgium
|
|
|
|
Can't you read the SQL Reference manual for such difficult query?
See on top IBM Manuals |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
do you want to concatenate two columns into one host variable?
what is your desired sort order? sorry, I was unable to determine the sort order
based on your example.
and please use the '[' code /code ']' tags, here is the URL:
ibmmainframes.com/faq.php?mode=bbcode |
|
| Back to top |
|
|
jaspal
New User
Joined: 22 May 2007
Posts: 68
Location: mumbai
|
|
|
|
| dbzTHEdinosauer wrote: |
do you want to concatenate two columns into one host variable?
what is your desired sort order? sorry, I was unable to determine the sort order
based on your example.
and please use the '[' code /code ']' tags, here is the URL:
ibmmainframes.com/faq.php?mode=bbcode |
|
|
| Back to top |
|
|
jaspal
New User
Joined: 22 May 2007
Posts: 68
Location: mumbai
|
|
|
|
Hi ,
Sorry for the previous post TYPO error:
Below query would give the required output:
| Quote: |
| select first name ||' '|| last name from emp table ; |
Use where clause if required.
Thanks.
Jaspal
919923696840 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you test this before posting? I believe this will return more spaces than are wanted. . . |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Amol,
Please try this.
| Code: |
| select STRIP(first_name) ||' '|| STRIP(last_name) from emp table ; |
|
|
| Back to top |
|
|
jaspal
New User
Joined: 22 May 2007
Posts: 68
Location: mumbai
|
|
|
|
Hi Srihari,
If you would run the below queries you will get same output:
| Code: |
Q1: select first name ||' '|| last name from emp table ;
Q2: select STRIP(first_name) ||' '|| STRIP(last_name) from emp table ; |
I am bit curious to know why you suggested to STRIP the name........?
I would really appreciate your response.
as per my understanding ,STRIP function removes blanks or another specified character from the end, the beginning, or both ends of a string expression.
Syntax: STRIP (String EXP,Leading/Trailing/Both,Strip Character);
Assume that the host variable BALANCE of type CHAR(9) has a value of '000345.50'.
Q3:
| Code: |
SELECT STRIP(:BALANCE, LEADING, '0'),
FROM emp table |
Output : Returns the value '345.50'.
Secondly,
If we will run the below query it will take a singe blank space:
| Code: |
| Select first name ||' '|| last name from emp table ; |
|
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| jaspal wrote: |
If we will run the below query it will take a singe blank space:
| Code: |
| Select first name ||' '|| last name from emp table ; |
|
What if first name and last name are loaded into the table with with trailing or leading spaces? If first name is a char(15) column with a value of 'JOHN' then your query will have more than 1 spaces between the first and last names! |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
Assuming First_name is of CHAR(10)
Results without STRIP
Results with STRIP
Hope it helps,
WTF |
|
| Back to top |
|
|
|
|
