Parse data for recurring value?

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  November 3, 2006 at 8:25 am

  #669806

  Pretty sure i do not want to run this against mucho rows but...

  declare @values varchar(1000), @sIdx1 int

  set @sIdx1 = 0

  declare @myVal table (id int identity, rptr varchar(10))

  set @values = ltrim(rtrim('7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 7104 7106 7112 7107 7132 7108 7150 7105 7110 7109 7111 2 '))

  while len(@values) > 0

  BEGIN

  select @sIdx1 = charindex(' ', @values)

  if @sIdx1 = 0

  set @sIdx1 = len(@values)

  insert into @myVal (rptr) values(left(@values, @sIdx1 - 1))

  set @values = right(@values, len(@values) - @sIdx1)

  END

  --=== query table variable for multiples

  select rptr, count(*) from @myVal group by rptr

• Peter E. Kierstead

  SSCarpal Tunnel

  Points: 4114

  More actions

  November 6, 2006 at 9:20 am

  #670102

  Parse the string into a temp table and do a:

  Select count(*) from dbo.#tmp group by ValueColumn having count(*)>1

  

  ---

  
  PeteK
  I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.