Get the numbers out of the brackets

[cheryl]

Hello Community,

I would like to get the numbers out of the field if the field value is in this format: O1234 [20210322], So if the field value starts with O with 3 numbers after, and then with 8 numbers in the bracket, then I would like to get the numbers out of the bracket.

For example, the Column name is 'Description', there could be many different formats in Description. If the Description column contains below values:

O1234 [20210322]

O1244 [20225632]

O1564 [20205682]

O1364 [20205672]

O9834 hw+ne

O9834 /20203945

Then the new field I would like to create will extract the value from 'Description' from the bracket:

20210322

20225632

20205682

20205672

null

null

Anybody have idea how to write this as a function in the Load Script (Back-end)?

Thanks a lot in advance.

Best,

Cheryl

[marcus_sommer · Accepted solution]

It could be done by combining multiple queries with one or several if-loops, like:

if(left(Field, 1) = 'O' and len(Field) = 16 and isnum(mid(Field,2,3)) and
len(textbetween(Field, '[', ']')) = 8 and isnum(textbetween(Field, '[', ']'))) and ...

[sidhiq91 · Accepted solution]

@cheryl  Since you are more focused to bring the text between the brackets.

NoConcatenate
Temp:
Load * inline [
Description
O1234 (20210322)

O1244 (20225632)

O1564 (20205682)

O1364 (20205672)

O9834 hw+ne

O9834 /20203945
] ;

NoConcatenate
Temp1:
Load *,
if(left(Description,1)='O',TextBetween(Description,'(',')'),'NULL') as Description_NEW
REsident Temp;

Drop Table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.