MSSQL - find immediate past iteration of field
If you're using SQL Server 2012 or later you can use LAG, although you'll need to handle the lack of normalization first:
SELECT
  SKU
 ,CASE
    WHEN SKUPrefix &lt;&gt; LAG(SKUPrefix,1,'') OVER (PARTITION BY 1 ORDER BY SKU) THEN ''
    ELSE LAG(SKU) OVER (PARTITION BY 1 ORDER BY SKU)
  END AS PriorSKU
FROM
  (
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
  FROM
    &lt;Your Table&gt;
  ) SKUFix

One might as &quot;Why not partition by SKUPrefix and avoid the CASE statement and the answer is if a suitable index of the table (clustered or otherwise) starts with SKU then the query will be able to execute without sorting the data.
Prior to 2012 things are... uglier:
WITH SKUTemp AS
(
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
   ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SKU) AS SKURank
  FROM
    &lt;Your Table&gt;
)
SELECT
  SKU.SKU
 ,PriorSKU.SKU AS PriorSKU
FROM
  SKUTemp SKU
LEFT JOIN
  SKUTemp PriorSKU
    ON PriorSKU.SKUPrefix = SKU.SKUPrefix
        AND PriorSKU.SKURank = SKU.SKURank - 1

DDL to test:
CREATE TABLE #SKUTest
(
  SKU   CHAR(8)   NOT NULL
)
GO

INSERT INTO #SKUTest
VALUES
  ('ABCD')
 ,('ABCD(B)')
 ,('ABCD(E)')
 ,('ABDX')
 ,('ABDX(B)')
 ,('ABDX(F)')
 ,('BCDF(C)')
 ,('BCDF(P)')
 ,('BCDX')
 ,('BCDX(B)')
 ,('BCDX(G)')
GO

Results:
SKU         | PriorSKU
ABCD        | NULL
ABCD(B)     | ABCD    
ABCD(E)     | ABCD(B) 
ABDX        | NULL
ABDX(B)     | ABDX    
ABDX(F)     | ABDX(B) 
BCDF(C)     | NULL
BCDF(P)     | BCDF(C) 
BCDX        | NULL
BCDX(B)     | BCDX    
BCDX(G)     | BCDX(B)