BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

how to remove extra space between two words


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
kumarbo7
Senior Member
Senior Member



Joined: 12 Jul 2007

Posts: 38



PostPosted: Tue May 11, 2010 3:52 pm 
Post subject: how to remove extra space between two words

Hello,

Environment:
Database :MS Sql server 2008
Application :Data services 3.2
OS : Windows XP

Is there any function(s) in Data Service or in SQL that I could use to help remove space or tabs in the middle of a string…for example
Code:
“SHARON                    ALILB,  M  “
If you notice where the comma is, everything before it is in the Last Name field. I would like to replace the spaces or tab with just one single space.

Please help me out to resolve the roadblock.

Thanks in advance.
kumar
Back to top
Bindaas BODS
Senior Member
Senior Member



Joined: 25 Mar 2010

Posts: 53


flag
PostPosted: Tue May 11, 2010 6:35 pm 
Post subject: Re: how to remove extra space between two words

try this..

replace_substr(replace_substr(Input.Column_name, ' ',''),'/t','')

hope this helps.
Back to top
kumarbo7
Senior Member
Senior Member



Joined: 12 Jul 2007

Posts: 38



PostPosted: Wed May 12, 2010 9:40 am 
Post subject: Re: how to remove extra space between two words

Thank you so much for your valuable input.

but I am getting output as

Code:
AARONABELL,M


where as am looking as

Code:
AARON ABELL,M


Could you please suggest me where exactly i need to modify in function.

Thanks in Advance.

Santosh
Back to top
Bindaas BODS
Senior Member
Senior Member



Joined: 25 Mar 2010

Posts: 53


flag
PostPosted: Thu May 13, 2010 12:14 pm 
Post subject: Re: how to remove extra space between two words

If the first Name and Last name are always seperated by a TAB the you can use the same thing but repalce TAB with Space

replace_substr(replace_substr(Input.Column_name, ' ',''),'/t',' ')
Back to top
jlynn73
Principal Member
Principal Member



Joined: 27 Oct 2009

Posts: 425
Location: DesMoines Iowa


flag
PostPosted: Fri May 14, 2010 10:18 am 
Post subject: Re: how to remove extra space between two words

You could use python in a user defined transform.

' '.join(<field_name>.split())

this splits the field into a list, then joins the list back together using one blank as the delimiter. If you have leading/trailing white space, you may need to strip() the field.

theres pretty much nothing you cant do in a python script inside a user defined transform icon_wink.gif
Back to top
kumarbo7
Senior Member
Senior Member



Joined: 12 Jul 2007

Posts: 38



PostPosted: Fri May 14, 2010 10:49 am 
Post subject: Re: how to remove extra space between two words

Thank you Bindaas BODS and jlynn73 for your valuable inputs.

I figured out by using the replace_substr() function.

now am getting last name, first name.

Thanks in advance.
Santosh
Back to top
aidelia
Principal Member
Principal Member



Joined: 02 Feb 2006

Posts: 307
Location: Rosario, Argentina


flag
PostPosted: Fri May 14, 2010 1:03 pm 
Post subject: Re: how to remove extra space between two words

Could you post your solution?

Thanks,


Andrés
Back to top
kumarbo7
Senior Member
Senior Member



Joined: 12 Jul 2007

Posts: 38



PostPosted: Mon May 17, 2010 9:47 am 
Post subject: Re: how to remove extra space between two words

Hello,
Please find below solution which i got proper output.

Code:
replace_substr(replace_substr(Input.Column_name, '  ', ' '), ' /t', ' ')


Thanks,
Santosh
Back to top
Ramana
Principal Member
Principal Member



Joined: 30 Apr 2009
ASUG Icon
Posts: 359
Location: Bangalore


flag
PostPosted: Mon May 17, 2010 9:11 pm 
Post subject: Re: how to remove extra space between two words

Hi,
Here iam sending the custom fuction what we are using in my flows to remove multiple spaces in between strings.

$FLDNAME=rtrim_blanks(ltrim_blanks( $FNAME));
while ( $FLDNAME like '% %')
begin
$FLDNAME2=replace_substr($FLDNAME,' ',' ');
$FLDNAME=$FLDNAME2;
end
return $FLDNAME;

Regards,
Ramana
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0563 seconds using 17 queries. (SQL 0.0130 Parse 0.0349 Other 0.0084)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo