Post reply

Extracting certain numbers from a string

  • October 8, 2019 at 1:08 pm

    Go to Answer

    #3686968

    Hi Guys,

    if object_id('tempdb..#Equipment') is not null
    drop table #equipment

    create table #equipment (Category varchar(20), ItemNumber varchar(30), ExpectedResult float)

    insert into #equipment (Category, ItemNumber, ExpectedResult)
    values  ('Cable', 'CB1/03BAE300FT', 300),
       ('Cable', 'CB1/03BAE050FT', 50),
       ('Cable', 'CB1/04BAE100FT', 100),
       ('Cable', 'CB1/04BAE150FT', 150),
       ('Cable', 'CB1/04BAE025FT', 25),
       ('Cable', 'CB1/04BAE050FT', 50),
       ('Cable', 'CB1/04BAE075FT', 75),
       ('Cable', 'CB1/04CNN050FT', 50),
       ('Cable', 'CB10/5BAE1000FT', 1000),
       ('Cable', 'CB10/5BAE100FT', 100),
       ('Cable', 'CB10/5BAE001M', 1),
       ('Cable', 'CB0164EXT012.5M', 2.5),
       ('Duct', 'DCAIR500MMG5M', 5),
       ('Duct', 'DCAIR500MMBK02M', 2),
       ('Duct', 'DCAIR500MMBK05M', 5),
       ('Duct', 'DC0500FLX005M', 5),
       ('Duct', 'DC0500FLX010M', 10),
       ('Duct', 'DC0500FLX020M', 20),
       ('Duct', 'DC0500HTEMP002M', 2),
       ('Hose', 'HS0015CTO2.5M', 2.5),
       ('Hose', 'HS0015CTO5.3M', 5.3),
       ('Hose', 'HS1LOPRUB10M', 10),
       ('Hose', 'HS0001FUEL10M', 10),
       ('Hose', 'HS0025FUE010M', 10),
       ('Hose', 'HS0001FUEL15M', 15),
       ('Hose', 'HS0001FUEL20M', 20),
       ('Hose', 'HS0001FUEL25M', 25),
       ('Hose', 'HS0001LPR010M', 10)

    select *
    from #equipment

    I have data in a SQL Server 2014 database similar to the test data above. I need to extract the length from the ItemNumber field to match the ExpectedResults field. The ItemNumber will always be in the format of [some text][some numbers][M/FT]. I need to extract all numbers before M or FT.

    Unfortunately the source system doesn't store this in a separate field and I have users spending way too much time doing this manually.

    Here was my attempt at doing it. I decided to stop at this point as my code wasn't very elegant and it was only going to get messier.

    select *,
    case when ItemNumber like '%1000FT' then '1000'
        when ItemNumber like '%FT' then try_convert(int,LEFT(RIGHT(ItemNumber,5),3))
        when ItemNumber like '%M' then try_convert(int,LEFT(RIGHT(ItemNumber,4),3)) end [Length]
    from #equipment

  • October 8, 2019 at 1:51 pm

    Answer

    #3686996 

    SELECTCategory, ItemNumber, ExpectedResult, ExtractedNumber
    FROM #equipment
    CROSS APPLY ( SELECT Item1 = STUFF( REVERSE(ItemNumber), 1, CASE WHEN ItemNumber LIKE '%M' THEN 1 WHEN ItemNumber LIKE '%FT' THEN 2 ELSE 0 END, '' ) ) t1
    CROSS APPLY ( SELECT ExtractedNumber = REVERSE(LEFT(Item1, CASE WHEN Item1 LIKE '[0-9]%' THEN PATINDEX('%[^0-9.]%', Item1) - 1 ELSE LEN(Item1) END) ) ) t2;

  • October 9, 2019 at 10:13 am

    #3687336

    Thank you Scott, appreciate your help.

Viewing 3 posts - 1 through 2 (of 2 total)

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