In Between text

[vardhan1305]

I need to create a column XXX where i should get the Number between the text.

I applied the logic TExtBEtween(name,'(',')') and i worked for just one scenario

Can anyone help me out.

id         state         name                                                  XXX

abc        BC       alb - ik me (abc def)(1234)                      1234

abc        BC       alb - ik me (abc def)(1234)                      1234

abc        BC       alb - ik me (1234)                                     1234

abc         BC      alb - ik me (1234)                                      1234

[neelamsaroha157 · Accepted solution]

Try - KeepChar(name, '0123456789')

[vardhan1305]

Hi Neelam. I got the output but sorry i forgot to write another scenario

id        state        name                                                  XXX

abc        BC      alb - ik me (abc def)(1234)                      1234

abc        BC      alb - ik me (abc def)(1234)                      1234

abc        BC      alb - ik me (1234)                                    1234

abc        BC      alb - ik me (1234)                                      1234

abc        BC      alb - ik me (pittsburgh)                          pittsburgh

I have 3 scenarios and can you suggest what to do in this scenario??

[neelamsaroha157]

So do you want the text/number between the last parenthesis??

[vardhan1305]

Yeah.   alb - ik me "   is the name and if there is any number in the  parenthesis (1234) it should display the number or else " pittsburgh  ".

KeepChar(name, '0123456789')  worked for 2 scenarios and i need to apply for the other one too.

name                                                            XXX

   alb - ik me (abc def)(1234)                         1234

     alb - ik me (abc def)(1234)                      1234

    alb - ik me (1234)                                      1234

    alb - ik me (1234)                                      1234

    alb - ik me (pittsburgh)                           pittsburgh

[santhana]

Hi Harsha Vardhan   Kindly use the below code  it will meet your desired output

data:

load * Inline [

id      ,   state      ,   name                                                 

abc     ,   BC    ,   alb - ik me (abc def)(1234)                     

abc    ,    BC     ,  alb - ik me (abc def)(12345)                    

abc     ,   BC    ,   alb - ik me (12346)                                    

abc      ,   BC   ,   alb - ik me (1234)

abc      ,   BC   , alb - ik me (pittsburgh)    ];

load   

id,

state,

name ,

purgechar(subfield(name,'(',-1),')') as XXXX,

'*' as temp  Resident   data ;

drop table   data;

Thanks and Regards

Santhana Kumar

[santhana]

Hi use this

purgechar(subfield(name,'(',-1),')') as XXXX,