Query to get only numbers from a string

[kpcollier]

I'm trying to find a function that will take a string and output only the numbers within that string.

My string field values look something like this:

C-123R    C-1 Ladders  R-32L

where the beginning of the string is always 2 characters before the numbers start. But, the numbers can be either 1-3 digits long, and the chars after the numbers can vary greatly.

I've tried a few different things like this and this but wasn't able to get it going. 

Any help is appreciated.

[kpcollier]

SUBSTRING([@field:Stored_In], PATINDEX('%[0-9]%', [@field:Stored_In]), PATINDEX('%[0-9][^0-9]%', [@field:Stored_In] + 't') - PATINDEX('%[0-9]%', [@field:Stored_In]) + 1)

This will work for the format my values are in.

I do not believe this will work for other formats, where there are multiple sequences of numbers with alpha values in between. 

[Tubby]

Hi @kpcollier

I have this workaround but I don't think this is the best way to do it. However, it works for all orientations/ formats. 

Try this in a formula field: 
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace([@field:text] ,'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J','') ,'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T','') ,'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),'$',''),',',''),' ','')

I just repeatedly wrapped the field in a Replace() so that all possible text inside will be omitted and replaced with nothing. You can add another layer to that if wish to add "-" to the values that needs to be removed.

Sample in my application:
 

Table Design:


I hope it helps...
~Tubbs