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
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
Thank you Scott, appreciate your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy