topic getting just 12345 from the below text. Attunity replicate global transformations in Qlik Replicate

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

kamal0475 — Wed, 04 Oct 2023 21:45:02 GMT

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

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

jcjames — Wed, 04 Oct 2023 23:28:18 GMT

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.

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

Heinvandenheuvel — Thu, 05 Oct 2023 03:27:45 GMT

@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!

🙂

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

kamal0475 — Thu, 05 Oct 2023 17:45:19 GMT

Thank you @jcjames the solution worked.

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

kamal0475 — Thu, 05 Oct 2023 17:46:10 GMT

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