Skip to main content
Question

replace string in SQL transformer with another string

fbastian73
Contributor
Forum|alt.badge.img+12

I want be able to replace part of a generic table name with the current table name in the SQL statement within the sql transformer.

for example <LAST_YR_RIN_SCHEMA> with R_2017_01_01

is this something that can be done in the workbench?

i was told to use a private parameter but that wouldnt work and also to use a creator followed by attribute_creator followed by the sql_executor which is also not good as i only want to replace part of the table name not the entire name as shown in screen shot above.

thanks for your suggestions.

5 replies

takashi
Influencer
  • takashiInfluencer
  • July 6, 2018

Hi @fbastian73, if you create an attribute that stores the entire SQL statement containing the placeholder '<LAST_YR_RIN_SCHEMA>', you can replace it with the StringReplacer and then set the attribute to the SQL Statement parameter in a SQLExecutor. This screenshot illustrates my intention.

Alternatively, you can also execute the @ReplaceString function in the SQLExecutor instead of using the StringReplacer, like this:

takashi
Influencer
  • takashiInfluencer
  • July 6, 2018
Where is your screenshot?

 

 

fbastian73
Contributor
Forum|alt.badge.img+12
  • fbastian73Contributor
  • Author
  • Contributor
  • July 8, 2018
takashi wrote:

Hi @fbastian73, if you create an attribute that stores the entire SQL statement containing the placeholder '<LAST_YR_RIN_SCHEMA>', you can replace it with the StringReplacer and then set the attribute to the SQL Statement parameter in a SQLExecutor. This screenshot illustrates my intention.

Alternatively, you can also execute the @ReplaceString function in the SQLExecutor instead of using the StringReplacer, like this:

hi @takashi

 

sorry, i was sure the screenshot had attached. Here it is again.

 

I will try your suggestion and see how i go.

 

As you can see, i want to replace only part of the table name not the entire name.

 

 

thanks for your help.

 

takashi
Influencer
  • takashiInfluencer
  • July 9, 2018
fbastian73 wrote:
hi @takashi

 

sorry, i was sure the screenshot had attached. Here it is again.

 

I will try your suggestion and see how i go.

 

As you can see, i want to replace only part of the table name not the entire name.

 

 

thanks for your help.

 

Thanks for posting the screenshot. I think that the method I suggested should also work even if the placeholder is only a part of the entire table name. If a single SQL statement contains two different placeholders, you can just use two StringReplacers in series.

 

 

fbastian73
Contributor
Forum|alt.badge.img+12
  • fbastian73Contributor
  • Author
  • Contributor
  • July 9, 2018
takashi wrote:
Thanks for posting the screenshot. I think that the method I suggested should also work even if the placeholder is only a part of the entire table name. If a single SQL statement contains two different placeholders, you can just use two StringReplacers in series.

 

 

hi @takashi,

 

it has worked for me with the first method you mentioned.

 

thanks again.

 

Reply


Helpful Members This Week

Community Stats

31,310
Posts
118,734
Replies
38,899
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.