Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ...
@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.
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 ...
@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.