Parsing a string

[vaibhav_j]

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"]

[javierecfpn · Accepted solution]

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]

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