Remove special characters and split into rows

[surajap123]

Hi There,

I wanted to remove $ character in the below values. The $ character within the string represents different values, which should be moved into separate rows.

Table:
LOAD *
INLINE [
Key, Value
58, $London city$New York$
66, $Tokyo$New Delhi$
];

Output:

Key, Value
58, London city
58, New York
66, Tokyo
66, New Delhi

Please help

[surajap123]

Hi There,

I wanted to remove $ character in the below values. The $ character within the string represents different values, which should be moved into separate rows.

Table:
LOAD *
INLINE [
Key, Value
58, $London city$New York$
66, $Tokyo$New Delhi$
];

Output:

Key, Value
58, London city
58, New York
66, Tokyo
66, New Delhi

Please help

[Kushal_Chawda · Accepted solution]

@surajap123  try below

Table:
load *
where len(trim(Value))>0;
LOAD Key,
     subfield(Value,'$') as Value
FROM table;

[surajap123]

I tried it, seems it didn't work.

Please help.

[Kushal_Chawda]

@surajap123  when you take  a resident load if columns are same then it will autoconcatenate with previous table so add noconcatenate before you do resident load

NoConcatenate
load *
where len(trim(Value))>0;
LOAD Key,
subfield(Value,'$') as Value
Resident Table;

DROP table Table;

[surajap123]

Thank you so much @Kushal_Chawda