Jump to content
  • 0

Query to get only numbers from a string



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.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
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. 

Link to comment
Share on other sites

  • 0

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...

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...