Wildcard in Triggered Actions?!

[Felix]

Hi everyone,

Is there a wildcard function in triggered actions?

I want to extract a number from a string but the number is not always in the same place. e.g. in both "strength of product is x percent" and "product strength x percent" I want to find x. 

I've tried using 'contains', 'find pattern' and 'find substring' but none of them respond to any of the normal wildcards. Surely there is a way to do this without having to have a formula field in the table.

Thanks for your help

Felix

[NiceDuck]

Do you mean the 'Find Pattern' block on the triggered actions?

I was able to use it properly using this as a reference: https://www.w3schools.com/sql/func_sqlserver_patindex.asp

My idea was to first, find the location of the first number using this: 

PATINDEX('%[0123456789]%','test1234colmsd')

 

It should return 5 so I know that the first number is located on the 5th position.

Now this time, reverse the text and use the same formula

PATINDEX('%[0123456789]%',reverse('test1234colmsd'))

Now this will return 7

If we combine them all on this setup:

substring('test1234colmsd',PATINDEX('%[0123456789]%','test1234colmsd'),(LEN('test1234colmsd')-PATINDEX('%[0123456789]%',reverse('test1234colmsd'))+2)-PATINDEX('%[0123456789]%','test1234colmsd'))        

You can rebuild this on a trigger or use it on a formula field. If you are going to use a formula field approach, replace the 'test1234colmsd' with the field reference of the one carrying the values.

substring([@field:valueholder],PATINDEX('%[0123456789]%',[@field:valueholder]),(LEN([@field:valueholder])-PATINDEX('%[0123456789]%',reverse([@field:valueholder]))+2)-PATINDEX('%[0123456789]%',[@field:valueholder]))      





 

[Felix]

Thanks @NiceDuck

[Flowers4Algernon]

Hello, just wanted to add this how-to link as they will be helpful as you go along: https://howto.caspio.com/tables-and-views/triggered-actions/