blank space within quotation

Formally known as "Mercator Inside Integrator 6.7", DataStage TX enables high-volume, complex transactions without the need for additional coding.

Moderators: chulett, rschirm

Post Reply
zaino22
Premium Member
Premium Member
Posts: 81
Joined: Thu Mar 22, 2007 7:10 pm

blank space within quotation

Post by zaino22 »

we are getting input values encapsulated in quoation marks in fixed length CSV file.

SAMPLE:

002,"ADM","ADDITIONAL,MANAGEMENT<SP><SP><SP><SP>", "ADMN"
002: begining of the record body
First value: "ADM"
Second value: "ADDITIONAL, MANAGEMENT<SP><SP><SP><SP><SP>"
Third value: "ADMN"

One of the input value contrains extra comma (part of the data), and have some blank spaces before the quotation ends. To take care of extra data, they added quotation around all the three fields, which is working well. but it doesnt remove blank spaces I have tried TRIMLEFT() and TRIMB() but it did not work, and XML contains spaces after value: MANAGEMENT (SEE THE SECOND INPUT VALUE).

Please let me know if there is any function in TX which would take care of spaces in between quotation marks.

please provide example.

thanks!
janhess
Participant
Posts: 201
Joined: Thu Sep 18, 2003 2:18 am
Location: UK

Post by janhess »

use SUBSTITUTE(fieldname," ","")
Last edited by janhess on Thu Sep 27, 2007 8:01 am, edited 1 time in total.
zaino22
Premium Member
Premium Member
Posts: 81
Joined: Thu Mar 22, 2007 7:10 pm

Post by zaino22 »

janhess wrote:use SUBSTITUTE(" ","")
where do I use substitute function: in a TYPE TREE or in a MAPPED FIELD of Map Designer?
I assum in a MAP Designer, correct me if i am wrong. In a map designer, I mapped a field to output card, field is: Country_Name Field:In1 (every single value of this field contains spaces, and i want to get rid of space). how to use SUSBSTITUTE with above field?

please provide example.

thankS!
janhess
Participant
Posts: 201
Joined: Thu Sep 18, 2003 2:18 am
Location: UK

Post by janhess »

in map rule.
=substitute(Country_Name Field:In1," ","")
zaino22
Premium Member
Premium Member
Posts: 81
Joined: Thu Mar 22, 2007 7:10 pm

Post by zaino22 »

janhess wrote:in map rule.
=substitute(Country_Name Field:In1," ","")
Big thanks to janhess for a qwick reply.

THANKS!!
janhess
Participant
Posts: 201
Joined: Thu Sep 18, 2003 2:18 am
Location: UK

Post by janhess »

try this if your field has other spaces in it

="<ANYQUOTE>" + trimright(substitute("""United State's of America ""","<QUOTE>","")) + "<ANYQUOTE>"

In your case replace the text with your field.
Last edited by janhess on Fri Sep 28, 2007 2:43 am, edited 1 time in total.
rep
Participant
Posts: 82
Joined: Tue Jun 19, 2007 8:04 am
Location: New York City

Post by rep »

zaino22 wrote:
janhess wrote:in map rule.
=substitute(Country_Name Field:In1," ","")
Big thanks to janhess for a qwick reply.

THANKS!!
You could always rate his post high and set the thread to "resolved" and give him a boost in points. Of course, this will put his farther ahead of me, but I like the competition.

ps: If there were any spaces in the field that you wanted, like let's say it was "ADDITIONAL MANAGEMENT", and you wanted the space between additional and management, then jahness's last post using the "TRIMRIGHT" function is what you want. It'll trim extra characters off the right side of the field, just as TRIMLEFT will trim...oh, I'm sure you get it.
zaino22
Premium Member
Premium Member
Posts: 81
Joined: Thu Mar 22, 2007 7:10 pm

thanks, it worked

Post by zaino22 »

Thanks, it worked. I will give you some pionts.
thanks again!

rep wrote:
zaino22 wrote:
janhess wrote:in map rule.
=substitute(Country_Name Field:In1," ","")
Big thanks to janhess for a qwick reply.

THANKS!!
You could always rate his post high and set the thread to "resolved" and give him a boost in points. Of course, this will put his farther ahead of me, but I like the competition.

ps: If there were any spaces in the field that you wanted, like let's say it was "ADDITIONAL MANAGEMENT", and you wanted the space between additional and management, then jahness's last post using the "TRIMRIGHT" function is what you want. It'll trim extra characters off the right side of the field, just as TRIMLEFT will trim...oh, I'm sure you get it.
Post Reply