This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 42%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESN%3C/text%3E%3C/svg%3E)
Hi,
I have a Foreach loop and within it is a copy activity to load data from blob to sql. Since there are multiple files and tables, i want to set the Sink table name to be dynamic but by very specific name.
Ex: Filename: 1234_SalesData_Prod.csv and i'd like the table name to be SalesData
Filename: Asfc234_MarketData_Prod.csv and i'd like the table name to be MarketData.
The _Prod will not change, the initial characters can change. I tried using an instr but i get the error 'instr' is not a recognized function. Please advise on how to achieve this instr and subtring combination.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
@sam nick if I helped resolve your issue, please mark as accepted answer. If you still need more assistance, please tell me how I may better assist.
@sam nick if you found your own soluton, please share it here with the community.
Hello @sam nick and welcome to Microsoft Q&A.
It looks like you want to extract the middle part of the file name.
If we can make the assumption that all your file names follow the form "part1_TableName_part3" , having exactly 3 parts, separated by _ , then we can construct an expression to retrieve the TableName.
@split(pipeline().parameters.filename,'_')[1]
Hi @MartinJaffer-MSFT - That solution would work only if i have 3 parts as you stated, but in reality the only common part is the "_Prod". Before that is could be multiple _'s as the names could be East_Market_Prod or Region_Prod etc.
in the case of East_Market_Prod, is the desired name "Market" or "East_Market" ? If the desired name is just "Market" then we just need to get the next to the last result of splitting on _. If the desired name is "East_Market" I do not know how to help, because of the case "123_East_Market_Prod" . How can we tell whether the "123" should be included?
If we just want to remove the "_Prod.csv" that can be done.
@replace( myString , '_Prod.csv','')
After removing the ending, apply whatever logic you have to separate the beginning from the desired table name.