June 16, 2016 at 8:10 am
For example...I have fields that return:
MG
MG/5ML
MCG/0.5ML
MG/ML
I just need to extract the numbers to do some multiplication off of those numbers
June 16, 2016 at 8:36 am
Hi,
This could help you
SELECT strVal, STUFF(
STUFF(strVal+'c', PATINDEX('%[0-9][^0-9.]%', strVal+'c')+1, LEN(strVal), ''),
1, PATINDEX('%[0-9]%', strVal)-1, ''
) AS [decimal]
FROM(VALUES( 'MG' ), ( 'MG/5ML' ), ( 'MCG/0.5ML' ), ( 'MG/ML' ), ( '55.5' )) AS t(strVal);
Igor Micev,My blog: www.igormicev.com
June 16, 2016 at 8:49 am
This could help you, too.
http://www.sqlservercentral.com/scripts/String+Function/141686/
June 16, 2016 at 8:50 am
just a word to the wise...and I am sure you already know this....but before doing any calculations be sure you undestand what UOM (unit of measure) you are dealing with 😉
SELECT strVal, STUFF(
STUFF(strVal+'c', PATINDEX('%[0-9][^0-9.]%', strVal+'c')+1, LEN(strVal), ''),
1, PATINDEX('%[0-9]%', strVal)-1, ''
) AS [decimal]
FROM(VALUES ( 'MCG/0.5ML' ), ( 'MCG/0.5LTR' ) ) AS t(strVal);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 16, 2016 at 9:04 am
And sorry if I wasn't clear....the field is called 'Strengthu' the examples I gave are the values. So, would I put Strengthu in the formula?
June 16, 2016 at 9:34 am
cory.bullard76 (6/16/2016)
And sorry if I wasn't clear....the field is called 'Strengthu' the examples I gave are the values. So, would I put Strengthu in the formula?
If you understand the formula, you'll be able to figure it out. If you don't, you shouldn't use it.
If you have questions on what a specific part is doing, ask them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply