Column splitter in workspace formula

Hello I have a Long string like this .

abcd.efgh.ijkl.plmn.zxcve

I want to split the column into multiple columns with formula like this (Dot as the splitter/separator )

abcd efgh  ijkl plmn zxcve

How to do that?

Any suggestions or solution very welcome. thanks. 

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Ashlin,

    You can use a combination of substr, strlen and strpos to split up a string present in that format. You can find more info on above mentioned formulas here - https://docs.thoughtspot.com/6.1/reference/formula-reference.html

     

    I believe this from our last conversation around segregating the table name that is in the form "databasename.schemaname.tablename".

    I was able to separate that out by creating the following formulas in this sequence:

     

    Temp : substr (table name, 17, strlen (table name)) 

     

    Database : substr (temp, 0, strpos (temp,'.')) 

     

    Schema: substr ( substr ( temp , 1+strlen ( database ) , strlen ( temp ) ) , 0, strpos ( substr ( temp , 1+strlen ( database ) , strlen ( temp ) ) , '.')) 

     

    Table Name : substr (table name, 19 +strlen (database ) + strlen ( schema ) , strlen ( table name ) ) 

     

    Let me know if this helps.

    Like 1
  • But first my question is how do you determine the table name and schema name. as they are like this falcon.**.***.schemaname.tablename

    there are many tables and schemas. hardcoding aint a good idea. isnt it . what i want to do is first split them based on . (dots) 

    Like
  • Ashlin Rajan or just get the tablename or schema name

    Like
  • Hi Ashlin Rajan ,  

    If you observe the pattern - each tablename comes in the format falcon.tableinfo.database.schema.tablename. Our aim is to extract the database, schema and tablename and ignore the rest from this string. If you see the formulas in the above thread:

    temp - It is basically used to get rid of the falcon.tableinfo from the full string value. 

    Then each formula for database, Schema and Tablename extract the relevant info using different formula function and already created temp formula. Let me know if you face any issue with the above formulas.

     

    Coming to your question on how to split them based on dots (.) , you can use strpos  and substr function to do that.

    For example - say you have a string :  String A = "United.States"

     strpos (string A, '.') basically returns the numeric position (starting from 0) of the first occurrence of the dot (.) in the first string.

     

    After identifying the numeric position of the dot (.) , you can use substr function that will slice the original string based on the starting point and the the length. 

    i.e  substr (string A, 0, strpos (string A,'.')) will fetch "United" out of the string "United.States". 

     

    Let me know if this helps.

    Like 2
  • Aditya Abhinav Thanks a lot

    Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 5Replies
  • 46Views
  • 2 Following