Substring Function in Magic ETL

Unknown

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

product_John

Thank you for submitting this idea @bcooper.  I am assigning to our product manager @mattchandler for review.

mattchandler

Thanks for the feedback @bcooper! We're not actually running SQL on the back-end, but the SQL-like substring functionality would definitley be helpful. I'll get this added to the engineering backlog.

 

Best,

Matt Chandler

Product Manager, Domo

mattchandler

fsalinas

Yes, please. 

GOFORIT5

Is there any update on this?  I want to select the left three of a column in ETL.  Thanks

NewsomSolutions

@bcooper and @GOFORIT5 did you realize they slipped this in a few weeks ago?

GOFORIT5

@NewsomSolutions No I did not!  Do you have information on how the string should read?  Thanks

NewsomSolutions

Here is what the feature is called in Magic ETL "String Operations", inside that you get the opportunity for substrings and trims.