In database Text to column replacement

[JeanetteF]

Hi always knowledgable Alteryx Community, 

I have a question regarding how to replace the text-to-columns tool, when needing to do everything with in-database tools. Further information is that the database I'm using is an EXASOL database. 

Example of the input data:

ID	Region
1	AE,AU,DE,KF,OS,OE
2	KS,PW,OF,JF,IR,FS,KM,ED,IF,UE,UF,WI,CK,WM,EL,KC,JE,WI,FU,DZ,IU,JF,EK,DM,CO,IF,WE

I have both the situation, where I need to split the data into both columns and rows. 

• For the rows all I can think of is doing a lot of substring functions in many separate formula tools and then in the end union them all together. This is not a very good and dynamic solution as there in some fields are over 100 splits, meaning 100 formula tools. No good.
• For columns it's sort of the same solution as above, but in the same formula tool. Still not very dynamic to make 100 now field manually with substring formula's. 

I also looked into using the sqlparsing.tokenize(sqlstring) function, that EXASOL themselves recommends but I have a hard time identifying how to use this function and which components to replace in their example, which is: 

CREATE SCRIPT example(sql_text) AS

    local tokens = sqlparsing.tokenize(sql_text)

    for i=1,#tokens do

          print(tokens[i])

    end

/
EXECUTE SCRIPT example('SELECT dummy FROM dual') WITH OUTPUT;

OUTPUT

I hope someone have the golden key to a great solution.

[SophiaF · Accepted solution]

Hi @JeanetteF,

Thank you for your contribution to the Community! At this time, this is not a supported Alteryx feature, and would require custom SQL as you mentioned. We hope that a fellow Community member is able to assist you with this question.

If relevant, we encourage you to submit a new Product Idea with your feedback. Check out the Ideas Submission Guidelines and Evaluation Criteria for additional instructions.