String Handling

veera24 · Post by **veera24** » Fri Dec 12, 2008 2:30 am

Hi all.
Some of the coulmns in a file needs to be in double quotes (say string data type columns) and the rest are not to be in the quotes. So is there any way of doin this?

I know one solution that we can suprees the quote character and then concatenate the quotes in the transformer coulmn level derivation wherever we want (i mean in the column level derivation of transformer). Is this only way of doing it or any thing else there?

If so kindly help me.
Your time would be highly appreciated.

Thanks in advance
Veera

ray.wurlod · Post by **ray.wurlod** » Fri Dec 12, 2008 3:46 am

You can set the quote character in the Format properties of a Sequential File stage. (Technically it's in the link properties.)

battaliou · Post by **battaliou** » Fri Dec 12, 2008 3:51 am

An example of your output data here would be useful.

If you simply require varchar to be quoted and numerics to be not in a sequential comma seperated file then set the quotes setting to "

Alternatively you could simply have 1 output string a varchar 9999 for example in which you construct your entire string.

battaliou · Post by **battaliou** » Fri Dec 12, 2008 3:52 am

An example of your output data here would be useful.

If you simply require varchar to be quoted and numerics to be not in a sequential comma seperated file then set the quotes setting to "

Alternatively you could simply have 1 output string a varchar 9999 for example in which you construct your entire string.

chulett · Post by **chulett** » Fri Dec 12, 2008 7:44 am

What you require should be handled automatically by the stage as long as the data types are defined correctly for each field.

veera24 · Post by **veera24** » Mon Dec 15, 2008 2:13 am

ray.wurlod wrote:You can set the quote character in the Format properties of a Sequential File stage. (Technically it's in the link properties.) ...

If that is the case, wont other columns will be having quotes?

As i said earlier i wanna have the quotes for particular columns as i need not all the columns. So pls. suggest. Or kindly let me know that the solution i've mentioned in my question will be right or any other way of doing that?

Thanks for your time ray

Your time will be highly appreciated.

Thanks in advance
Veera

veera24 · Post by **veera24** » Mon Dec 15, 2008 2:15 am

chulett wrote:What you require should be handled automatically by the stage as long as the data types are defined correctly for each field. ...

How come it will be correct?Could you pls. elaborate with an example so that i can easily understand because of you

Your time will be highly appreciated

Thanks in advance,
Veera

chulett · Post by **chulett** » Mon Dec 15, 2008 8:04 am

When writing, 'string' columns are automatically quoted while 'numeric' fields are not. Why not simply try it and see if it works the way you need?

veera24 · Post by **veera24** » Thu Dec 18, 2008 1:13 am

chulett wrote:When writing, 'string' columns are automatically quoted while 'numeric' fields are not. Why not simply try it and see if it works the way you need? ...

Hi Chulett,
Thanks a lot for your advice. I've tried it and i got the output as you've mentioned earlier and also as i wanted. Thanks again for your time.

Thanks in advance,
Veera

veera24 · Post by **veera24** » Sun Dec 21, 2008 11:45 pm

chulett wrote:When writing, 'string' columns are automatically quoted while 'numeric' fields are not. Why not simply try it and see if it works the way you need? ...

Hi chulett,
I dont want the quotes for string and as well as timestamp column. how do i can achieve this?because by default the column will have double quotes as it is non-numeric field.Am i right?

Kindly suggest me.

Thanks in advance,
veera.

veera24 · Post by **veera24** » Sun Dec 21, 2008 11:48 pm

chulett wrote:When writing, 'string' columns are automatically quoted while 'numeric' fields are not. Why not simply try it and see if it works the way you need? ...

Hi chulett,
I dont want the quotes for string and as well as timestamp column. how do i can achieve this?because by default the column will have double quotes as it is non-numeric field.Am i right?

Kindly suggest me.

Thanks in advance,
veera.

chulett · Post by **chulett** » Mon Dec 22, 2008 8:13 am

veera24 wrote:I dont want the quotes for string and as well as timestamp column.

I have no idea what you want or don't want here.

What is being quoted right now that you don't want quoted and why do they bother you? All fields can be quoted without issue or causing anyone any problems, hence the question.

kandyshandy · Post by **kandyshandy** » Mon Dec 22, 2008 10:35 am

Veera, In the format tab, enter 000 for quote character box !! Nothing will be quoted

chulett · Post by **chulett** » Mon Dec 22, 2008 10:49 am

Obviously, they want or need something quoted or this conversation would have been over quite some time ago.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 22, 2008 2:04 pm

Use 000 for the quote character on the Format, and use SQuote() function for each field that needs to be quoted. This allows individual fields to be quoted without all string fields to be quoted.