title: Replace suffix/ending on the Product name in query for grouping
body: <p>In our database system we have some products that have the same name but have different endings. </p>
  
  <p>Examples:</p>
  
  <pre>
                              2012    2013
  Produkt namn xyz ab 2002    0       2
  Produkt namn xyz cd 2004    5       3
  Produkt namn xyz ef 2002    2       1
  Produkt namn xyz gh 2006    3       0
  </pre>
  
  <p>We would like to group them under one name.</p>
  
  <pre>
  Produkt namn xyz           10       6
  </pre>
  
  <p>We have created an query or stored procedure which is the source of a report. I used the REPLACE function to group similar products but in this case it is to many different so it would be useful to use wildcard with REPLACE like this:</p>
  
  <pre>
  REPLACE(product_name, 'Product name xyz%', 'Product name xyz')
  </pre>
  
  <p>Then we could group similar products under one product name.</p>
  
  <p>Unfortunately it does not work that way.</p>
  
  <p>Does anyone have a good idea on how to solve it?</p>
  
  <p>EDIT:
  Perhaps I was unclear. We have a lot of different products with different names and different length of the names.</p>
  
  <p>New example list:</p>
  
  <pre>                            2012           2013
                              units sold    units sold
  Produkt namn kkkkkkk mmmm     7            9
  Produkt namn xyz ab 2002      0            2
  Produkt namn xyz cd 2004      5            3
  Produkt namn AAAAA MMMM NN    6            8
  Produkt namn xyz ef 2002      2            1
  Produkt namn xyz gh 2006      3            0
  Produkt namn ABC 123 anything 4            9
  </pre>
  
  <p>Find this part (Produkt namn xyz" cd 2004") of this particular product and replace with nothing to group them under one name for summary.</p>
  
  <pre>
  Produkt namn xyz             10            6
  </pre>
  
  <p>My code so far:</p>
  
  <pre><code>ALTER PROC [dbo].[uspOnAndOffTrade]
      (
          @PeriodStart AS INT
      ,   @PeriodEnd AS INT
      ,   @CurPerStart AS INT -- Ny 140510
      ,   @PrePerEnd AS INT -- Ny 140510
      ,   @PreYr AS INT = NULL
      ,   @CurYr AS INT = NULL
      ,   @FamiljTyp AS INT = NULL
      ,   @ProdNo AS INT = NULL
      ,   @ProdDescr AS VARCHAR(MAX) = NULL
      ,   @ProducentName AS VARCHAR(MAX) = NULL
      ,   @CustomerName AS VARCHAR(MAX) = NULL
      ,   @Land AS VARCHAR(MAX) = NULL
      ,   @Region AS VARCHAR(MAX) = NULL
      )
  AS
  BEGIN
      WITH qAllTradeSumTot
      AS (
      SELECT     pt.InvoDt
                  ,  REPLACE(
          REPLACE(
              REPLACE(p.Descr, 'Product Name xxx 12345', 'Product Name xxx 750 ml'),
                  'Product Name 2004', 'Product Name'), 'Product Name 2002', 'Product Name')  AS 'Product'
                  , p.Inf AS 'Producent'
                  , a.Nm AS 'Customer', p.Gr4, a.R9, 
                      p.R8 AS 'Familj', pt.PrTp, p.ProdTp4, 
                            dbo.ProdCat.Descr AS 'Land', ProdCat_1.Descr AS 'Region', 
                            p.R8, pt.TrTp, 
                            CASE WHEN p.Gr4 = 0 THEN CAST(ROUND(SUM(pt.NoInvoAb * 1), 0) AS INT) 
                            ELSE CAST(ROUND(SUM(pt.NoInvoAb * p.Gr4), 0) AS INT) END AS 'SumU', LEFT(pt.InvoDt, 4) 
                            AS 'Yr'
      FROM         dbo.ProdCat INNER JOIN
                            dbo.Prod AS p ON dbo.ProdCat.PrCatNo = p.PrCatNo INNER JOIN
                            dbo.ProdTr AS pt ON p.ProdNo = pt.ProdNo INNER JOIN
                            dbo.ProdCat AS ProdCat_1 ON p.PrCatNo2 = ProdCat_1.PrCatNo INNER JOIN
                            dbo.Actor AS a ON pt.CustNo = a.CustNo
      WHERE     ((a.R9 = '1') OR (a.R9 = '2'))
              AND (pt.Price &lt;&gt;0)
              AND (pt.InvoDt BETWEEN @PeriodStart AND @PeriodEnd) 
              AND (@FamiljTyp IS NULL OR p.R8 = @FamiljTyp)
              AND (@CustomerName IS NULL OR a.Nm LIKE '%' + @CustomerName + '%')
              AND (@ProdNo IS NULL OR p.ProdNo = @ProdNo)
              AND (@ProdDescr IS NULL OR p.Descr LIKE '%' + @ProdDescr + '%')
              AND (@ProducentName IS NULL OR p.Inf LIKE '%' + @ProducentName + '%')
              AND (@Land IS NULL OR ProdCat.Descr LIKE '%' + @Land + '%')
              AND (@Region IS NULL OR ProdCat_1.Descr LIKE '%' + @Region + '%')
      GROUP BY pt.InvoDt, p.Descr, p.Inf, a.Nm, p.Gr4, a.R9, p.R8, pt.TrTp, 
          LEFT(pt.InvoDt, 4), p.ProdTp4, dbo.ProdCat.Descr, ProdCat_1.Descr, pt.PrTp
      )
      SELECT ats.Producent, Product,
          SUM(CASE WHEN (InvoDt BETWEEN @PeriodStart AND @PrePerEnd) AND R9 = '1' THEN SumU ELSE 0 END) AS [OffTrade1],
          SUM(CASE WHEN (InvoDt BETWEEN @CurPerStart AND @PeriodEnd) AND R9 = '1' THEN SumU ELSE 0 END) AS [OffTrade2],
  
          SUM(CASE WHEN (InvoDt BETWEEN @PeriodStart AND @PrePerEnd) AND R9 = '2' THEN SumU ELSE 0 END) AS [OnTrade1],
          SUM(CASE WHEN (InvoDt BETWEEN @CurPerStart AND @PeriodEnd) AND R9 = '2' THEN SumU ELSE 0 END) AS [OnTrade2],
  
          SUM(CASE WHEN (InvoDt BETWEEN @PeriodStart AND @PrePerEnd) THEN ats.SumU ELSE 0 END) AS [Tot1],
          SUM(CASE WHEN (InvoDt BETWEEN @CurPerStart AND @PeriodEnd) THEN ats.SumU ELSE 0 END) AS [Tot2]
  
      FROM qAllTradeSumTot AS ats
      GROUP BY Producent, Product
      HAVING (SUM(CASE WHEN (InvoDt BETWEEN @PeriodStart AND @PrePerEnd) THEN ats.SumU END) &gt;= 0 )
      OR (SUM(CASE WHEN (InvoDt BETWEEN @CurPerStart AND @PeriodEnd) THEN ats.SumU END) &gt;= 0)
      ORDER BY Product;
  END
  </code></pre>
  
  <p>Hope it is clearer now!</p>
  
  <p>Thanks for all your help!</p>
  
  <p>Kind regards
  /martin</p>