Is there a way to extract certain parts of data

Question

Post reply

Is there a way to extract certain parts of data

cory.bullard76

Ten Centuries

Points: 1232
More actions
June 16, 2016 at 8:10 am

#317285

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

This could help you, too.

http://www.sqlservercentral.com/scripts/String+Function/141686/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

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

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 4

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?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2