Parse numbers from Column

Question

Parse numbers from Column

Viewing 4 posts - 16 through 18 (of 18 total)

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

Jason A. Long - Tuesday, April 11, 2017 1:45 PM
jcelko212 32090 - Tuesday, April 11, 2017 12:23 PM
dba-512085 - Friday, April 7, 2017 4:16 PM
I have a sql table that stores weight in one column. How would I go about extracting lbs and ozs into separate fields?
Many thanks in Advance!
14 lb 7.6 oz
8 lb 2.4 oz
21 lb 0.4 oz
13 lb 4.2 oz
29 lb 1.8 oz
9 lb 15.8 oz
19 lb 2.0 oz
26 lb 8.2 oz
22 lb 14.0 oz
33 lb 2.2 oz
This is one of the problems with antique measurement systems. I'm sure you really want to recalibrate your speedometer on your car to "furlongs per fortnight", to be totally out of touch with everybody. In the front end, before use insert data into the database, convert everything to a single minimal unit that can be expressed in decimal. This would seem to be decimal ounces. Then do the pounds, hundred weights, stones, and whatever other absurd unit of measure, your client wishes to see in a view or presentation layer.
I am so ashamed the United States not going metric back under Pres. Ford. Life could've been worse, I guess; the UK still could be on pins and shillings instead of decimals currency.
I'm not sure how you're holding the OP responsible for the way this data is being stored... From what I can tell, they've noticed that the current system is lacking and taking steps to rectify the problem.
For all we know, the OP wants this text column parsed out into the numeric values so that they can convert to metric...

I don't really understand your problem with the use of non metric measurements either. Yes, fine, it's a little "more confusing" than decimalised measurements to those that don't understand them, but it's really not that hard. As Jason pointed out, the OP clearly has recognised the problem with the way the data is currently stored and wants to resolve that. Is it really that much of a problem having two columns to store your measurement in (Pounds & Ounces) as opposed to one (kilograms)? No. Speed and storage wise the difference is going to be negligible.

I find myself often working in Feet and inches and in Metres. Do I have any problems doing so? No, none.

If, however, you're so bothered about not having a metric value on there, we can always add it on. 🙂
WITH Pats AS ( SELECT W.TotalWeight, PATINDEX('%lb%',W.TotalWeight) AS PoundsPos, PATINDEX('%oz%',W.TotalWeight) AS OuncesPos FROM #Weights W), Split AS ( SELECT TotalWeight, CASE WHEN PoundsPos = 0 THEN 0 ELSE LEFT(TotalWeight, PoundsPos -1) END AS Pounds, CASE WHEN OuncesPos = 0 THEN 0.0 WHEN PoundsPos = 0 THEN CAST(LEFT(TotalWeight, OuncesPos -1) AS decimal(3,1)) ELSE CAST(SUBSTRING(TotalWeight, PoundsPos + 3, OuncesPos - (PoundsPos + 3) -1) AS decimal(3,1)) END AS Ounces FROM Pats) SELECT S.TotalWeight, S.Pounds, S.Ounces, ((S.Pounds * 16) + S.Ounces) * 0.0283495 AS Kilograms FROM Split S;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

jcelko212 32090 - Tuesday, April 11, 2017 12:23 PM
dba-512085 - Friday, April 7, 2017 4:16 PM
I have a sql table that stores weight in one column. How would I go about extracting lbs and ozs into separate fields?
Many thanks in Advance!
14 lb 7.6 oz
8 lb 2.4 oz
21 lb 0.4 oz
13 lb 4.2 oz
29 lb 1.8 oz
9 lb 15.8 oz
19 lb 2.0 oz
26 lb 8.2 oz
22 lb 14.0 oz
33 lb 2.2 oz
This is one of the problems with antique measurement systems. I'm sure you really want to recalibrate your speedometer on your car to "furlongs per fortnight", to be totally out of touch with everybody. In the front end, before use insert data into the database, convert everything to a single minimal unit that can be expressed in decimal. This would seem to be decimal ounces. Then do the pounds, hundred weights, stones, and whatever other absurd unit of measure, your client wishes to see in a view or presentation layer.
I am so ashamed the United States not going metric back under Pres. Ford. Life could've been worse, I guess; the UK still could be on pins and shillings instead of decimals currency.

Heh... and yet the metric system does heavily rely on a couple of ancient measurement systems that are older than the hills and one of them is based on an indeterminate decimal value and the others are non-10 based and it's not likely that will ever change.

As for absurd units of measure, I'm perfectly content with ordering a half rack of ribs, a yard of ale, and 3 large eggs over easy. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

Jeff Moden - Wednesday, April 12, 2017 7:47 AM
jcelko212 32090 - Tuesday, April 11, 2017 12:23 PM
dba-512085 - Friday, April 7, 2017 4:16 PM
I have a sql table that stores weight in one column. How would I go about extracting lbs and ozs into separate fields?
Many thanks in Advance!
14 lb 7.6 oz
8 lb 2.4 oz
21 lb 0.4 oz
13 lb 4.2 oz
29 lb 1.8 oz
9 lb 15.8 oz
19 lb 2.0 oz
26 lb 8.2 oz
22 lb 14.0 oz
33 lb 2.2 oz
This is one of the problems with antique measurement systems. I'm sure you really want to recalibrate your speedometer on your car to "furlongs per fortnight", to be totally out of touch with everybody. In the front end, before use insert data into the database, convert everything to a single minimal unit that can be expressed in decimal. This would seem to be decimal ounces. Then do the pounds, hundred weights, stones, and whatever other absurd unit of measure, your client wishes to see in a view or presentation layer.
I am so ashamed the United States not going metric back under Pres. Ford. Life could've been worse, I guess; the UK still could be on pins and shillings instead of decimals currency.
Heh... and yet the metric system does heavily rely on a couple of ancient measurement systems that are older than the hills and one of them is based on an indeterminate decimal value and the others are non-10 based and it's not likely that will ever change.
As for absurd units of measure, I'm perfectly content with ordering a half rack of ribs, a yard of ale, and 3 large eggs over easy. 😉

Which "Yard of Ale" are you drinking? US, British, Queen Ann, etc? My rule of thumb is to use the biggest one I can get 😉

https://en.wikipedia.org/wiki/Comparison_of_the_imperial_and_US_customary_measurement_systems

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

jcelko212 32090 - Wednesday, April 12, 2017 11:03 AM
Which "Yard of Ale" are you drinking? US, British, Queen Ann, etc? My rule of thumb is to use the biggest one I can get 😉
https://en.wikipedia.org/wiki/Comparison_of_the_imperial_and_US_customary_measurement_systems

My rule of thumb is "Whatever they happen to have:. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)