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 out the values for further analysis. The challenge is that not every string has the same length and I almost feel like I need some sort of programming looping structure to loop through each element of the array and extract individual values from it. In the examples below, I need to extract values that are within [ and ]. Any thoughts and ideas are much appreciated. Thank you!

[1,"0"],[16,"8938473"],[17,"0"],[21,"*"]

[2,"40000"],[16,"35000"]

[1,"0"],[14,"1554025"],[15,"0"],[20,"*"],[40,"0"]

[1,"0.00"],[12,"0.00"],[13,"0.00"],[21,"0.00"],[41,"0.00"]

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

2 Replies

Replies have been turned off for this discussion
  • 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