Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
A table in one of my databases has a varchar field in which there is multiple data that require parsing.
The format in which the data is in that varchar field is in the format: {"<visit_type_id>":"[<expected_visits_overall>,<expected_visits_cycle_1>,<expected_visits_cycle_2>,<expected_visits_cycle_3>]"}
There can be one or more visit_type_ids with the corresponding expected_visits, separated by commas.
Example for a single visit_type_id: {"10":"[0,0,0,0]"}
Example for multiple visit_type_ids: {"10":"[0,0,0,0]","7":"[1,2,3,4]","6":"[5,6,7,8]","5":"[9,10,11,12]"}
How can I parse this varchar into either an array or a new table?
An example of table I would like to obtain is(for the multiple visit_type_ids example I provided above):
Visit_Type_ID | Expected_Visits_Overall | Expected_Visits_Cycle_1 | Expected_Visits_Cycle_2 | Expected_Visits_Cycle_3 |
---|---|---|---|---|
10 | 0 | 0 | 0 | 0 |
7 | 1 | 2 | 3 | 4 |
6 | 5 | 6 | 7 | 8 |
5 | 9 | 10 | 11 | 12 |
How about:
LOAD
SubField(Segment, ',', 1) as visit_type_id,
SubField(Segment, ',', 2) as expected_visits_overall,
SubField(Segment, ',', 3) as expected_visits_cycle_1,
SubField(Segment, ',', 4) as expected_visits_cycle_2,
SubField(Segment, ',', 5) as expected_visits_cycle_3
;
LOAD
Replace(Purgechar(SubField(dbfield, '","'),'"{}[]'),':',',') as Segment
;
SQL SELECT dbfield FROM ...;
-Rob
How we are getting data? XML Format / JSON Format?
The data is stored in a table from the database that I am connected to.
This is difficult to read as per my analysis. For a work around, You could try. Make sure - This should parse 5 parameters only with in array..
SET Measure = Replace(
Replace(
Replace(
SubField(
Trim(
Replace(
Replace(
Replace(
Replace(
'{"10":"[0,0,0,0]","7":"[1,2,3,4]","6":"[5,6,7,8]","5":"[9,10,11,12]"}',
'{',' '),
'}',' '),
'"',' '),
':','@')
),
']'),
'[', ' '),
'@', ' '),
',', ' ');
For i=1 to 5
LOAD SubField($(Measure), $(i), ' ') as expected_visits_overal$(i)
AutoGenerate $(i);
Next i
How about:
LOAD
SubField(Segment, ',', 1) as visit_type_id,
SubField(Segment, ',', 2) as expected_visits_overall,
SubField(Segment, ',', 3) as expected_visits_cycle_1,
SubField(Segment, ',', 4) as expected_visits_cycle_2,
SubField(Segment, ',', 5) as expected_visits_cycle_3
;
LOAD
Replace(Purgechar(SubField(dbfield, '","'),'"{}[]'),':',',') as Segment
;
SQL SELECT dbfield FROM ...;
-Rob
I'm trying to understand how this works. SubField creates a new in-memory table ?
SubField() extract substrings from a string:
-Rob