April 7, 2017 at 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
April 8, 2017 at 10:15 am
With a lot of data, this won't be that quick, but:CREATE TABLE #Weights
(TotalWeight varchar(20));
GO
INSERT INTO #Weights
VALUES
('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'),
('10 lb'), --Incase you have weights with no ounces
('6.9 oz'); --And incase you have no pounds.
GO
WITH Pats AS (
SELECT W.TotalWeight,
PATINDEX('%lb%',W.TotalWeight) AS PoundsPos,
PATINDEX('%oz%',W.TotalWeight) AS OuncesPos
FROM #Weights W)
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;
GO
DROP TABLE #Weights;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 10, 2017 at 11:29 am
Let's try using a string splitting function "DelimitedSplit8K" to do the heavy lifting:CREATE TABLE #Weights (
WeightID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TotalWeight varchar(20)
);
INSERT INTO #Weights (TotalWeight)
VALUES ('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'),
('10 lb'), --Incase you have weights with no ounces
('6.9 oz'); --And incase you have no pounds.
WITH ALL_WEIGHTS AS (
SELECT *,
LEAD(S.Item, 1, NULL) OVER(PARTITION BY W.WeightID ORDER BY S.ItemNumber) AS Measure
FROM #Weights AS W
CROSS APPLY dbo.DelimitedSplit8K(W.TotalWeight, ' ') AS S
),
FILTERED_DATA AS (
SELECT AW.WeightID, AW.TotalWeight, AW.Item, AW.Measure,
CASE AW.Measure WHEN 'lb' THEN 1 WHEN 'oz' THEN 2 END AS RN
FROM ALL_WEIGHTS AS AW
WHERE AW.ItemNumber % 2 = 1
),
FULLY_FLESHED AS (
SELECT FD.WeightID, FD.RN, FD.TotalWeight, CAST(FD.Item AS decimal(6,1)) AS Qty, FD.Measure
FROM FILTERED_DATA AS FD
UNION ALL
SELECT FD2.WeightID, CASE FD2.Measure WHEN 'lb' THEN 2 WHEN 'oz' THEN 1 END AS RN,
FD2.TotalWeight, 0 AS Qty, CASE FD2.Measure WHEN 'lb' THEN 'oz' WHEN 'oz' THEN 'lb' END AS Measure
FROM FILTERED_DATA AS FD2
WHERE NOT EXISTS (SELECT 1 FROM FILTERED_DATA AS FD3 WHERE FD3.WeightID = FD2.WeightID AND FD3.RN <> FD2.RN)
)
SELECT FF.WeightID, FF.TotalWeight,
MAX(CASE FF.RN WHEN 1 THEN FF.Qty ELSE 0 END) AS LbQty,
MAX(CASE FF.RN WHEN 2 THEN FF.Qty ELSE 0 END) AS OzQty
FROM FULLY_FLESHED AS FF
GROUP BY FF.WeightID, FF.TotalWeight
ORDER BY FF.WeightID;
DROP TABLE #Weights;
The splitting function is available here: http://www.sqlservercentral.com/articles/72993/
Read the article - it's an excellent treatise on the topic.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 10, 2017 at 2:37 pm
Here is a different version using Steve's test data set...
It makes for a cleaner plan and a lower lower cost estimate but I haven't tested against a large enough data set to know for certain that it will yield significantly better execution times...
IF OBJECT_ID('tempdb..#Weights', 'U') IS NOT NULL
DROP TABLE #Weights;
GO
CREATE TABLE #Weights (
WeightID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TotalWeight VARCHAR(20) NOT NULL
);
GO
INSERT INTO #Weights (TotalWeight)
VALUES
('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'),
('10 lb'), --Incase you have weights with no ounces
('6.9 oz'); --And incase you have no pounds.
WITH
cte_SplitValues AS (
SELECT
w.WeightID,
ds.ItemNumber,
ds.Item
FROM
#Weights w
CROSS APPLY dbo.DelimitedSplit8K(w.TotalWeight, ' ') ds
),
cte_LabelValuePairs AS (
SELECT
sv.WeightID,
Label = sv.Item,
Value = LAG(sv.Item, 1) OVER (PARTITION BY sv.WeightID ORDER BY sv.ItemNumber),
sv.ItemNumber
FROM
cte_SplitValues sv
),
cte_PairedCleanup AS (
SELECT
lvp.WeightID,
lvp.Label,
lvp.Value
FROM
cte_LabelValuePairs lvp
WHERE
lvp.ItemNumber % 2 = 0
)
SELECT
pc.WeightID,
w.TotalWeight,
Pounds = MAX(CASE WHEN pc.Label = 'lb' THEN CAST(pc.Value AS INT) ELSE 0 END),
Ounces = MAX(CASE WHEN pc.Label = 'oz' THEN CAST(pc.Value AS DECIMAL(9,1)) ELSE 0 END)
FROM
cte_PairedCleanup pc
JOIN #Weights w
ON pc.WeightID = w.WeightID
GROUP BY
pc.WeightID,
w.TotalWeight;
April 10, 2017 at 6:58 pm
dba-512085 - Friday, April 7, 2017 4:16 PMI 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
FIrst, read the first link in my signature line under "Helpful Links" for future posts. Thanks for helping us help you in the future.
Second, we need to know what the PK is for whatever table you're getting the rows from. Or at least I do. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2017 at 1:54 am
Jeff Moden - Monday, April 10, 2017 6:58 PMdba-512085 - Friday, April 7, 2017 4:16 PMI 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 ozFIrst, read the first link in my signature line under "Helpful Links" for future posts. Thanks for helping us help you in the future.
Second, we need to know what the PK is for whatever table you're getting the rows from. Or at least I do. 😉
+1
If the data format is consistent then
LEFT([column],PATINDEX('% lb %',[column])-1),
REPLACE(SUBSTRING([column],PATINDEX('% lb %',[column])+4,255),' oz','')
Far away is close at hand in the images of elsewhere.
Anon.
April 11, 2017 at 2:29 am
Jason A. Long - Monday, April 10, 2017 2:37 PMHere is a different version using Steve's test data set...
It makes for a cleaner plan and a lower lower cost estimate but I haven't tested against a large enough data set to know for certain that it will yield significantly better execution times...
IF OBJECT_ID('tempdb..#Weights', 'U') IS NOT NULL
DROP TABLE #Weights;
GOCREATE TABLE #Weights (
WeightID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TotalWeight VARCHAR(20) NOT NULL
);
GOINSERT INTO #Weights (TotalWeight)
VALUES
('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'),
('10 lb'), --Incase you have weights with no ounces
('6.9 oz'); --And incase you have no pounds.WITH
cte_SplitValues AS (
SELECT
w.WeightID,
ds.ItemNumber,
ds.Item
FROM
#Weights w
CROSS APPLY dbo.DelimitedSplit8K(w.TotalWeight, ' ') ds
),
cte_LabelValuePairs AS (
SELECT
sv.WeightID,
Label = sv.Item,
Value = LAG(sv.Item, 1) OVER (PARTITION BY sv.WeightID ORDER BY sv.ItemNumber),
sv.ItemNumber
FROM
cte_SplitValues sv
),
cte_PairedCleanup AS (
SELECT
lvp.WeightID,
lvp.Label,
lvp.Value
FROM
cte_LabelValuePairs lvp
WHERE
lvp.ItemNumber % 2 = 0
)
SELECT
pc.WeightID,
w.TotalWeight,
Pounds = MAX(CASE WHEN pc.Label = 'lb' THEN CAST(pc.Value AS INT) ELSE 0 END),
Ounces = MAX(CASE WHEN pc.Label = 'oz' THEN CAST(pc.Value AS DECIMAL(9,1)) ELSE 0 END)
FROM
cte_PairedCleanup pc
JOIN #Weights w
ON pc.WeightID = w.WeightID
GROUP BY
pc.WeightID,
w.TotalWeight;
Compared to mine, it still seems low. Query weight @ 100,000 rows is massive on yours compared to mine (although, a big improvement over Steve's 🙂 ).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2017 at 7:19 am
Jason A. Long - Monday, April 10, 2017 2:37 PM...
Compared to mine, it still seems low. Query weight @ 100,000 rows is massive on yours compared to mine (although, a big improvement over Steve's 🙂 ).
Yea... In this case parsing the strings into rows is an unnecessary expense. See what you think of the following...
SELECT
w.WeightID,
w.TotalWeight,
Pounds = CAST(SUBSTRING(w.TotalWeight, 1, lb.LbPos)AS INT),
Ounces = CAST(ISNULL(NULLIF(SUBSTRING(w.TotalWeight, ozs.OzStart, oz.OzPos - ozs.OzStart), ''), '0') AS DECIMAL(9,2))
FROM
#Weights w
CROSS APPLY ( VALUES (CHARINDEX(' lb', w.TotalWeight, 1)) ) lb (LbPos)
CROSS APPLY ( VALUES (CHARINDEX(' oz', w.TotalWeight, 1)) ) oz (OzPos)
CROSS APPLY ( VALUES (CASE WHEN lb.LbPos = 0 OR oz.OzPos = 0 THEN 0 ELSE lb.LbPos + 4 END) ) ozs (OzStart)
April 11, 2017 at 7:32 am
dba-512085 - Friday, April 7, 2017 4:16 PMI 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
the way you posted your sample data indicates that there is a consistent single space between the numbers and the UOM? Is this always the case?
for example would you ever have data such as this
14lb 7.6oz --- note no spaces
or for that matter any other possibilities....assuming this column is a (n)varchar column and maybe allow unstructured entries.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 11, 2017 at 7:42 am
Thom A - Tuesday, April 11, 2017 2:29 AMJason A. Long - Monday, April 10, 2017 2:37 PM...Compared to mine, it still seems low. Query weight @ 100,000 rows is massive on yours compared to mine (although, a big improvement over Steve's 🙂 ).
Yea... In this case parsing the strings into rows is an unnecessary expense. See what you think of the following...
SELECT
w.WeightID,
w.TotalWeight,
Pounds = CAST(SUBSTRING(w.TotalWeight, 1, lb.LbPos)AS INT),
Ounces = CAST(ISNULL(NULLIF(SUBSTRING(w.TotalWeight, ozs.OzStart, oz.OzPos - ozs.OzStart), ''), '0') AS DECIMAL(9,2))
FROM
#Weights w
CROSS APPLY ( VALUES (CHARINDEX(' lb', w.TotalWeight, 1)) ) lb (LbPos)
CROSS APPLY ( VALUES (CHARINDEX(' oz', w.TotalWeight, 1)) ) oz (OzPos)
CROSS APPLY ( VALUES (CASE WHEN lb.LbPos = 0 OR oz.OzPos = 0 THEN 0 ELSE lb.LbPos + 4 END) ) ozs (OzStart)
More or less identical query plans 🙂 Only difference is your rows are 4 bytes bigger due to WeightID being returned ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2017 at 8:05 am
Many ways to do this, here's another:
IF OBJECT_ID('TempDB..#Weights') IS NOT NULL DROP TABLE#Weights
CREATETABLE #Weights (TotalWeight varchar(20));
INSERT INTO #Weights
VALUES
('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.0oz'),
('33 lb 2.2 oz'),
('10 lb'), --Incase you have weights with no ounces
('6.9 oz'); --Andin case you have no pounds.
SELECT TotalWeight,
[lb]= CAST('0'+ISNULL(LEFT(TotalWeight,NULLIF(q,0)-2),0) AS SMALLINT),
[oz]=CAST('0'+ISNULL(SUBSTRING(TotalWeight,ISNULL(NULLIF(q,0),-3)+3,4),LEFT(TotalWeight,4))AS NUMERIC(3,1))
FROM #Weights
CROSS APPLY (SELECT q = CHARINDEX('lb',TotalWeight)) x
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2017 at 8:09 am
maybe I am wrong (probably:P) but I don't think I have seen a solution that correctly parsesINSERT INTO #Weights
VALUES
('14lb 7.6oz') ---- note no spaces between number and UOM
maybe the OP doesn't require this...maybe they do?
just a comment <grin>
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 11, 2017 at 8:13 am
J Livingston SQL - Tuesday, April 11, 2017 8:09 AMmaybe I am wrong (probably:P) but I don't think I have seen a solution that correctly parsesINSERT INTO #Weights
VALUES
('14lb 7.6oz') ---- note no spaces between number and UOMmaybe the OP doesn't require this...maybe they do?
just a comment <grin>
Fair point G - but the OP posted the data like this: 14 lb 7.6 oz.
It's a trivial matter to put the spaces in, if they're not already there 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2017 at 12:23 pm
dba-512085 - Friday, April 7, 2017 4:16 PMI 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.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 11, 2017 at 1:45 pm
jcelko212 32090 - Tuesday, April 11, 2017 12:23 PMdba-512085 - Friday, April 7, 2017 4:16 PMI 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 ozThis 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...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply