Parse numbers from Column

• dba-512085

  SSC Eights!

  Points: 876

  More actions

  April 7, 2017 at 4:16 pm

  #401911

  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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 8, 2017 at 10:15 am

  #1937316

  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 10, 2017 at 11:29 am

  #1937485

  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)

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  April 10, 2017 at 2:37 pm

  #1937514

  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;


• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  April 10, 2017 at 6:58 pm

  #1937528

  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

  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

  ---

  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)
  

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  April 11, 2017 at 1:54 am

  #1937554

  Jeff Moden - Monday, April 10, 2017 6:58 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

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

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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 11, 2017 at 2:29 am

  #1937559

  Jason A. Long - Monday, April 10, 2017 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;


  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

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  April 11, 2017 at 7:19 am

  #1937599

  Thom A - Tuesday, April 11, 2017 2:29 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)


• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 11, 2017 at 7:32 am

  #1937602

  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

  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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  April 11, 2017 at 7:42 am

  #1937609

  Jason A. Long - Tuesday, April 11, 2017 7:19 AM

  Thom A - Tuesday, April 11, 2017 2:29 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)


  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 11, 2017 at 8:05 am

  #1937619

  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]

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  April 11, 2017 at 8:09 am

  #1937622

  maybe I am wrong (probably:P) but I don't think I have seen a solution that correctly parses
  INSERT 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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 11, 2017 at 8:13 am

  #1937623

  J Livingston SQL - Tuesday, April 11, 2017 8:09 AM

  maybe I am wrong (probably:P) but I don't think I have seen a solution that correctly parses
  INSERT 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>

  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 🙂

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• jcelko212 32090

  SSCrazy Eights

  Points: 9294

  More actions

  April 11, 2017 at 12:23 pm

  #1937702

  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.

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

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  April 11, 2017 at 1:45 pm

  #1937714

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