Extracting certain numbers from a string

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  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

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  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;
  

• Jim-S

  SSCommitted

  Points: 1823

  More actions

  October 9, 2019 at 10:13 am

  #3687336

  Thank you Scott, appreciate your help.