using two characters as delimiters in subfield

[diwaskarki]

I have a long string that looks like this:

Application Name | (somestring, somestring, somestring ), application name2 | (somestring1, somestring1, somestring1 ), application name3 | (somestring1, somestring1, somestring1 ),

Application Name |(somestring, somestring, somestring ) needs to be in row1

application name2 | (somestring, somestring, somestring ) needs to be row2 and so on.

How can I use the closing parenthesis and comma as a delimiter in the subfield function?

[sunny_talwar]

May be just use this

SubField(FieldName, ',') as FieldName

[diwaskarki]

Just realized I made an error in my question. I'll make the edit. The string in the paranthesis itself will contain several commas so I cant do that.

[sunny_talwar · Accepted solution]

How about this

Table:

LOAD *

Where Trim(NewFieldName) <> ')';

LOAD *,

Trim(SubField(FieldName, '),') & ')') as NewFieldName;

LOAD * INLINE [

    FieldName

    "Application, Name | (my entitlement name), application, name2 | (entitlement name 2), application name3 | (entitlement name 4),"

];

[diwaskarki]

Thank you Sunny bhai, it seemed to do the trick. Can you please explain what the WHERE clause does?

[sunny_talwar]

When you remove the Where clause, you will see that one extra row was generated with ')'... so I removed that using the where clause

[diwaskarki]

Got it. But the problem is I have one more field in the same table where I use subfield function. I think because of the where clause, the subfield function on that field does not work. Is there a way to do a where for this field also. I am using ',' as a delimiter on this one.

[sunny_talwar]

Would you be able to share an updated sample where we can see the problem?

[diwaskarki]

Load

Where TRIM([Separate ASAR Entitlements]) <> ')' & Trim([Separated Removed Roles]);

Trim(Subfield([New Removed Entitlements],'),') &')') As [Separate ASAR Entitlements],

Trim(Subfield([New Removed Roles],',')) AS [Separated Removed Roles],

This is a snippet of my script.

Because of that where clause the second subfield function does not work.