Page 1 of 1
add single quote in data
Posted: Mon May 19, 2008 3:09 pm
by vsi
Hi
my requirement data comming like this
column1
abc~cdfg~tx
add single quotes in data where ~ comes like 'abc'~'cdfg'~'tx'
In data may be n number of ~
Any helps, advices would be high appreciate.
Thank you in advance
Posted: Mon May 19, 2008 4:32 pm
by sunny12345
If you know the Maximum occurances of ~ then you can use Field of Function...define as many stage variables as occurances of ~
Field(DSlink.Value,"~",1)
Field(DSlink.Value,"~",2)
Field(DSlink.Value,"~",1)
-----
------
So on
Then Concatinate all these stage variables with '~'
Posted: Mon May 19, 2008 5:46 pm
by ray.wurlod
You can convert each "~" to "'~'" using an Ereplace() function [search the forum] and concatenate a single quote character to each end of the string.
Or, if the source is a file, you could pre-process it with a stream editor such as sed (I note you are on Windows, but assume you have MKS Toolkit or similar available).
If the source is a database table, you can use SQL functions to generate the required delimiters.
Posted: Tue May 20, 2008 8:56 am
by vsi
HI Ray,
thanks for your reply.
in parallel i am not seeing Ereplace function. In parellel any other way to use similar function?
input and out put is a sequential file
thanks
ray.wurlod wrote:You can convert each "~" to "'~'" using an Ereplace() function [search the forum] and concatenate a single quote character to each end of the string.
Or, if the source is a file, you could pre-process it with a stream editor such as sed (I note you are on Windows, but assume you have MKS Toolkit or similar available).
If the source is a database table, you can use SQL functions to generate the required delimiters.
add single quote in data
Posted: Tue May 20, 2008 1:41 pm
by savis
Hi,
If this is the only column available in input or if this is the only column having ~ characters coming in input , use
cat |sed "s/~/'~'/g"
in the filter command(Sequential file)
Input: abc~cdfg~tx
Output:abc'~'cdfg'~'tx
If you want the output data as 'abc'~'cdfg'~'tx', then use a stage variable say quote with value "'" and in transformer use
quote:<Input_col_name>:quote
where quote is the stage variable having value as " ' ".
Note:
[We can also include the option quote=single]
[I even tried the option squote, but if there is single quote within the field then this command adds a \ before each single quote.
i.e) input sdsdsd output : 'sdsdsd'
input sd'sd'sd output : 'sd\'sd\'sd'
]
Gurus,
Please correct me if i am wrong.
Thanks,
Savis
Re: add single quote in data
Posted: Tue May 20, 2008 2:11 pm
by vsi
Hi Savis,
thanks for your reply,
i given
cat |sed "s/~/'~'/g" at input level and i used "'":input_column:"'"
out put is coming correctly
but
1. when i open the input file first getting blank popup window and then able to view the data
2. job is aborted status and getting this error=> Filter status 65,280
please let me know any suggetions.
thanks
savis wrote:Hi,
If this is the only column available in input or if this is the only column having ~ characters coming in input , use
cat |sed "s/~/'~'/g"
in the filter command(Sequential file)
Input: abc~cdfg~tx
Output:abc'~'cdfg'~'tx
If you want the output data as 'abc'~'cdfg'~'tx', then use a stage variable say quote with value "'" and in transformer use
quote:<Input_col_name>:quote
where quote is the stage variable having value as " ' ".
Note:
[We can also include the option quote=single]
[I even tried the option squote, but if there is single quote within the field then this command adds a \ before each single quote.
i.e) input sdsdsd output : 'sdsdsd'
input sd'sd'sd output : 'sd\'sd\'sd'
]
Gurus,
Please correct me if i am wrong.
Thanks,
Savis
Posted: Tue May 20, 2008 4:39 pm
by ray.wurlod
Search the forum for a parallel version of Ereplace().