how to remove extra space between two words

[kumarbo7]

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

[Bindaas BODS]

try this..

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

hope this helps.

[kumarbo7]

Thank you so much for your valuable input.

but I am getting output as

[Bindaas BODS]

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',' ')

[jlynn73]

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

[kumarbo7]

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

[aidelia]

Could you post your solution?

Thanks,


Andrés

[kumarbo7]

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

[Ramana]

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