getting just 12345 from the below text. Attunity replicate global transformations

[kamal0475]

I have a text message abc:abc678:12345. the length of the text will vary for different texts. I want to get all the text before the second special character":"

2 Examples below:

abc:abc678:12345

I want-> abc:abc678

kit:kit6789:12345

I want-> kit:kit6789

[jcjames · Accepted solution]

Hi @kamal0475 ,

This could potentially be a solution. AFAIK, SQLite does not support finding the nth occurrence of a string using INSTR function. So it becomes a bit complicated.

substr($FIRSTNME,1, instr($FIRSTNME, ':'))||substr((substr($FIRSTNME,instr($FIRSTNME, ':') + 1)),1, instr((substr($FIRSTNME,instr($FIRSTNME, ':') + 1)), ':') - 1)

Replace $FIRSTNME with your column. All the best.

[Heinvandenheuvel · Accepted solution]

@jcjames Yes, you have to pick up pieces. 

But it can be simplified to :

substr($FIRSTNME,1, 
       instr($FIRSTNME, ':') +
       instr(
       substr($FIRSTNME,instr($FIRSTNME, ':')+1,99), ":") -1)

What I do hear is to calculate the length f the desired section taking the length of the first chunk (with its colon) and add  the place of the second colon found in  a second chunk.  That second chunk is an  'everything' - simplified to 99 - substring from the main starting at  end of the first colon (substr).  Subtract 1 for the colon itself.

Seems to work for me! 

🙂

[kamal0475]

Thank you @jcjames  the solution worked.

[kamal0475]

Thank you @Heinvandenheuvel . The solution worked and very simple to understand. Thank you both.