Substring Function in Magic ETL

There should be a feature to do the typical substring manipulation in Magic ETL. For example, I have had multiple instances where we have a date that is formatted as a string such as '20150101'. There is no easy way in Magic ETL to call out that the first 4 characters are the year, the 2 characters starting at the 5th index in the string are month, and the last two characters are day.

 

In another example, let's say we have a string of City and State something like 'Seattle, Washington' or 'Salt Lake City, Utah'. There should be function that could return all the substring from the beginning to the first sign of a comma so we could separate the city and a function to grab everything from the beginning of the comma to the end of the string.

 

Basically, we would just need the functionality of a substring(), substring_index(), and char_index() type of a function. 

 

See this Stack Overflow for example of SQL code you might need for this:

http://stackoverflow.com/questions/22510624/split-city-state-zip-from-one-column-into-3-separate-also-add-dash-to-9-digit

Tagged:
25
25 votes

· Last Updated

Comments

This discussion has been closed.