|
View previous topic :: View next topic
|
| Author |
Message |
sivatechdrive
Active User
.jpg)
Joined: 17 Oct 2004
Posts: 191
Location: hyderabad
|
|
|
|
a column has the value like
mainframes india
but i need to concatenate & get it like ...mainframesindia
using Db2 function ie..i need to remove the space
Thanks
Siva |
|
| Back to top |
|
 |
ovreddy
Active User
.jpg)
Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.
|
|
|
|
Mr Siva,
You can use TRANSLATE or REPLACE function to solve this problem. for eg. a column name conatains first and last name combined to gether as 'Shiva Kumar' you need to concat both as 'ShivaKumar'. The following query will solve your problem....
SELECT TRANSLATE(NAME,'',' ') from TABXXX;
1 parameter - name of column
2 Parameter - empty string
3 parameter - Blank space
Please check it....
Regards,
O.V.Reddy. |
|
| Back to top |
|
|
anandinmainframe
Active User
Joined: 31 May 2007
Posts: 171
Location: India
|
|
|
|
Mr Siva,
I tried to concatanate using the query u mentioned below, bt i ended unsuccessfully. can u please help me to concate two fields (for eq field1 'suresh', field 2 'daniel' as 'suresh daniel) and not as ' suresh daniel'. there must be a single space between the two names |
|
| Back to top |
|
|
Nimesh.Srivastava
New User
Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE
|
|
|
|
anandinmainframe,
you can concatenate two fields like
| Quote: |
| (for eq field1 'suresh', field 2 'daniel' as 'suresh daniel) and not as ' suresh daniel' |
field1 = suresh
field2 = daniel
then
| Code: |
select '''' || field1 || field2 from table;
or
select concat(concat('''',field1),field2) from table;
output would be
'sureshdaniel
|
Hope this helps
Nimesh |
|
| Back to top |
|
|
twissi
Active User
Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ
|
|
|
|
But this deosn't answer the original question.. 
How do you remove an embeded SPACE from a single field?? |
|
| Back to top |
|
|
twissi
Active User
Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ
|
|
|
|
Siva,
Since nobody has given the solution, here is mine:
if the space appears in the same position all the time, you could use the following logic:
| Code: |
SELECT SUBSTR(field1,1,10) CONCAT SUBSTR(field1,12,5)
FROM table WHERE <condition> |
If SPACE has no fixed position, emm...we'll have to think again.
Cheers, Twissi. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
All of these are tested in DB2 V8
| Code: |
--Remove spaces within one column
select replace(description,' ','')
from owner.Description_table
--Combine 2 columns
select status || description
from owner.Description_table
--Combine 2 columns an remove spaces
select replace (status || description,' ','')
from owner.Description_table
|
|
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| Quote: |
a column has the value like
mainframes india
but i need to concatenate & get it like ...mainframesindia
|
My above solution will combine a single field into one long string like you requested. See --Remove spaces within one column.
| Quote: |
can u please help me to concate two fields (for eq field1 'suresh', field 2 'daniel' as 'suresh daniel) and not as ' suresh daniel'. there must be a single space between the two names |
Is solved with the below code. It combines 2 columns placing a space between them.
| Code: |
--Combine 2 columns with a space in the middle
select FirstName || ' ' || LastName
from SomeTable
|
|
|
| Back to top |
|
|
|
|
