A little Closer now just need to move the 'E' over and I will be set... HELP! lol
DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100'
-- HOTEL5-E 100
SELECT LTRIM(RTRIM(REPLACE(LEFT(@VendItemNumber, CHARINDEX('-', @VendItemNumber)),'-' ,'-')))
,LTRIM(RTRIM(REPLACE(SUBSTRING(@VendItemNumber, CHARINDEX('-', @VendItemNumber), LEN(@VendItemNumber) - CHARINDEX('-', @VendItemNumber) + 1),'-' ,'')))
Something like this?
DECLARE @VendItemNumber varchar(100) = 'HELLO5-E-100';
SELECT LEFT(@VendItemNumber,LEN(@VendItemNumber) - CHARINDEX('-',REVERSE(@VendItemNumber))), RIGHT(@VendItemNumber,CHARINDEX('-',REVERSE(@VendItemNumber)) - 1);