Can Talend use an external file to search and replace values

One Star

Can Talend use an external file to search and replace values

I have a list of about 80 values that I need to check within a data fields in a SQL Server database and replace the values with something different. For instance in a couple of address fields I want to change Street to ST, Road to RD, Avenue to Ave, etc. I am using a tReplace to find and replace smaller sets of values but I am looking for a solution that would use a longer list of values. Is there a way of doing this that someone could suggest and give an example for?
Also, is Talend case sensitive in a windows environment? In my file would I have to enter "STREET", "Street" and "street" or could I just use "Street" to search for the value I want to replace?
My project uses java and uses a SQL Server database.
Employee

Re: Can Talend use an external file to search and replace values

This is exactly what tReplaceList does. And yes it can be case insensitive or not, depending on your will (I mean there is a checkbox), not on the environment you're running on.
One Star

Re: Can Talend use an external file to search and replace values

This solution sounds great but I seem to have a problem implementing it. I am using java and I'm not sure if this is a perl only solution. I found the user documentation for tReplacelist and I am trying to implement it without receiving the results I expect. My goal is to change names to abreviations for street names. For instance from "123 Main Street" I would like to get "123 Main ST". In my delimited file I have the entry
" STREET ";"ST". I have tried with " STREET ";"ST" and "STREET";"ST". I would like to change values within a text string and not the entire string. Am I missing something?
One Star

Re: Can Talend use an external file to search and replace values

After some more experimenting, I found that I should not use double quotes(") in the delimited file. I updated my delimited file and the replacement works great!
Five Stars

Re: Can Talend use an external file to search and replace values

Hi
hi i am using tReplaceList, but it is creating problem, if My Look-up file has string like
ShortName|FullName
NA | NAME
ME | Meet End
but first look-up finds the NA and Replace with "NAME" in main stream, but same "NAME" string contains "ME" so also replace with "Meet End" and so on
then out put looks like " NAMeet End" this is a small example.
please suggest how to replace once and then move for next main row?