find the substring and to store the same substring value in new column.

[Arunesh_Saravanan]

Hi guys,

I need to find the substring from the table column and to store the same substring value in new column.

I do no how to raise the question.

if the Label Column as a value like below pic.

 

I need to find the value "Prd", "dev " and if it is "Prd" then Prd should be stored in new column as Instance. same like if it is "dev" then dev value should be stored in the same column as Instance. I need the script to run in data load editor. so that the table should be visible like below,

Labels                        Instance

["default","Prd"]             Prd

["Prd","default"]             Prd

["default","dev"]             dev

["default"]                       undefined

Is it make any sense? Kindly give any ideas or your thoughts/opinion on this.

Thanks.

[Kushal_Chawda · Accepted solution]

@Arunesh_Saravanan  If you know the exact value of string to be searched then you can try something like below

if(SubStringCount(Labels,'Prd'),'Prd',
if(SubStringCount(Labels,'dev'),'dev','undefined')) as Instance 

[Arunesh_Saravanan]

Super bro. Its working... Thanks.

and also, i had another doubt. I want to know how to extract the substring data with values and need to store the value in new column. In this case, I don't know the exact value of the string.

For example, Below column contains so many values in the string. But I need only Job_type, schema and frequency hour. This substring contains value so that i need to find those substrings and need to store those value in a new column.

so, the answer should be like below and unknown values should be Blank.

Could you suggest any idea for this?

[Arunesh_Saravanan]

Super bro. Its working... Thanks.

and also, i had another doubt. I want to know how to extract the substring data with values and need to store the value in new column. In this case, I don't know the exact value of the string.

For example, Below column contains so many values in the string. But I need only Job_type, schema and frequency hour. This substring contains value so that i need to find those substrings and need to store those value in a new column.

so, the answer should be like below and unknown values should be Blank.

Could you suggest any idea for this?