Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Concatenate a string in DB2 by removing spaces

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sivatechdrive

Active User


Joined: 17 Oct 2004
Posts: 190
Location: hyderabad

PostPosted: Mon Dec 06, 2004 3:18 pm    Post subject: Concatenate a string in DB2 by removing spaces
Reply with quote

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
View user's profile Send private message

ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon Dec 06, 2004 6:14 pm    Post subject: Here is U R Answer..
Reply with quote

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
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Wed Jul 18, 2007 11:03 am    Post subject:
Reply with quote

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
View user's profile Send private message
Nimesh.Srivastava

New User


Joined: 30 Nov 2006
Posts: 78
Location: SINGAPORE

PostPosted: Wed Jul 18, 2007 12:59 pm    Post subject: Re: concatenate
Reply with quote

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
View user's profile Send private message
twissi

Active User


Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ

PostPosted: Wed Jul 18, 2007 5:11 pm    Post subject:
Reply with quote

But this deosn't answer the original question.. icon_confused.gif

How do you remove an embeded SPACE from a single field??
Back to top
View user's profile Send private message
twissi

Active User


Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ

PostPosted: Wed Jul 18, 2007 9:21 pm    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Jul 19, 2007 12:39 am    Post subject:
Reply with quote

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
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Jul 19, 2007 12:45 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts To trim spaces in a pipe delimited da... Nish84 COBOL Programming 16 Mon Oct 10, 2016 1:54 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Removing Extra Spaces in a comma sepa... Puspojit DFSORT/ICETOOL 3 Tue Sep 13, 2016 7:46 am
No new posts Random Password (in string format) ge... ezhavendhan COBOL Programming 10 Mon Aug 29, 2016 3:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us