Forum Discussion

vaibhav_j's avatar
vaibhav_j
Cloud Apps
11-25-2021
Solved

Parsing a string

Hello! I am fairly new to Sisense and hope to get some insights on how best to solve a string parsing problem. The following are some examples of long strings/array that I need to parse and separate ...
Information Technology Analytics
  • javierecfpn's avatar
    javierecfpn
    11-25-2021

    As far as I know there is no built in split function which is what you need, but you can get a similar behavior using the text manipulation functions that are provided

    select
    strbetween(strparts(x, ',', 1),'[','|') x, strbetween(strparts(x, ',', 1),'|',']') y, 1 as pos
from (
    select replaceAll('[1,"0"],[16,"8938473"],[17,"0"],[21,"*"]', ',"', '|"') as x
)dt
union all
select
    strbetween(strparts(x, ',', 2),'[','|') x, strbetween(strparts(x, ',', 2),'|',']') y, 2 as pos
from (
    select replaceAll('[1,"0"],[16,"8938473"],[17,"0"],[21,"*"]', ',"', '|"') as x
)dt

    see: https://support.sisense.com/kb/en/article/split-a-delimited-field-into-rows for another example

javierecfpn's avatar
javierecfpn
Cloud Apps
11-25-2021

As far as I know there is no built in split function which is what you need, but you can get a similar behavior using the text manipulation functions that are provided

select
    strbetween(strparts(x, ',', 1),'[','|') x, strbetween(strparts(x, ',', 1),'|',']') y, 1 as pos
from (
    select replaceAll('[1,"0"],[16,"8938473"],[17,"0"],[21,"*"]', ',"', '|"') as x
)dt
union all
select
    strbetween(strparts(x, ',', 2),'[','|') x, strbetween(strparts(x, ',', 2),'|',']') y, 2 as pos
from (
    select replaceAll('[1,"0"],[16,"8938473"],[17,"0"],[21,"*"]', ',"', '|"') as x
)dt

see: https://support.sisense.com/kb/en/article/split-a-delimited-field-into-rows for another example

  • vaibhav_j's avatar
    vaibhav_j
    Cloud Apps
    11-29-2021

    javierecfpn Thank you! This is helpful. I appreciate your quick response on this.