Duplicate replace?

• amdavis

  SSC-Addicted

  Points: 445

  More actions

  January 23, 2014 at 9:27 am

  #284777

  In my query I come across this

  machine1Microsoft Office Professional Edition 2003

  machine1Microsoft Office Professional Plus 2010

  I want to say if I find 2003 and 2010 for machine1 then display only 2003 result.

  Help!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 23, 2014 at 9:32 am

  #1682850

  Will the year always come at the end of the string? Or would something like "Microsoft Office 2003 Professional Edition" is possible?

  The reason is because the solution can be simplified with a RIGHT

  WITH SampleData( Machine, Software) AS(

  SELECT 'machine1','Microsoft Office Professional Edition 2003' UNION ALL

  SELECT 'machine1','Microsoft Office Professional Plus 2010'

  --UNION ALL

  --SELECT 'machine1','Microsoft Office 2007 Professional ' UNION ALL

  --SELECT 'machine1','Microsoft Office Professional Edition 97'

  ),

  rowCTE AS(

  SELECT *,

  ROW_NUMBER() OVER(PARTITION BY Machine ORDER BY RIGHT( Software, 4)) rn

  FROM SampleData

  )

  SELECT Machine, Software

  FROM rowCTE

  WHERE rn = 1;

  This code will work fine with the information you provided. But if you add the commented rows, it might not work as expected.

  There's an option to find "integers" within the string and order by them using Jeff Moden's splitter[/url]. Here's an example:

  WITH SampleData( Machine, Software) AS(

  SELECT 'machine1','Microsoft Office Professional Edition 2003' UNION ALL

  SELECT 'machine1','Microsoft Office Professional Plus 2010' UNION ALL

  SELECT 'machine1','Microsoft Office 2007 Professional ' UNION ALL

  SELECT 'machine1','Microsoft Office Professional Edition 97'

  ),

  rowCTE AS(

  SELECT s.*,

  split.Item AS Edyear,

  ROW_NUMBER() OVER(PARTITION BY s.Machine ORDER BY CAST(split.Item AS INT)) rn

  FROM SampleData s

  CROSS APPLY dbo.DelimitedSplit8K( Software, ' ') split

  WHERE Item NOT LIKE '%[^0-9]%'

  AND Item > ''

  )

  SELECT Machine, Software

  FROM rowCTE

  WHERE rn = 1;

  However, depending on your real information this might not work completely if you have multiple numbers in your string or you'll have to change the code to have versions that include subversions (V11.5.1).

  The best thing to do is to normalize your data[/url] and avoid more problems.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2