Ignore Brackets () and load as two different fields

[psk180590]

Hello All,

I have the following data in my database as a single field.

(84700) Pharmacy.

From this field i would like to extract Two fileds.

84700 as ID and Pharmacy as Group.

Please, note i would like to ignore the brackets () here. 

Can someone please provide some pointers.

TIA

Sai.

[jonathandienst · Accepted solution]

If the source field is [myfield]:

...
Purgechar(SubField(myfield, ' ', 1), '()') as ID,
SubField(myfield, ' ', 2) as Group,
...

[tcullinane]

assuming you dont have brackets you want to keep in any place in the field
use subfield() to split and replace() to remove the extra leading bracket so;
replace(subfield(FieldName,')',1),'(','') as ID,
trim(subfield(FieldName,')',2)) as Group

[erick_ws]

Hi,
You can use:
TextBetween(SINGLE_FIELD, '(' , ')') AS ID, //Getting the text between '(' and ')'
MID(SINGLE_FIELD, index(SINGLE_FIELD, ' ')+1) AS Group //Getting the substring from the original field, assuming that there will be always an SPACE between the ID and the Group. (the "+1" is to get the string 1 position after the SPACE, or else it was going to bring ' Pharmacy')

[Ivan_Bozov]

LOAD
PURGECHAR(PURGECHAR(SUBFIELD(Field, ' ', 1),'('),')') AS ID,
SUBFIELD(Field, ' ', 2) AS Group
FROM...

Oh, well, so many people were faster than me. 😄