substract the word from the list

• karthikeyan-146504

  Ten Centuries

  Points: 1055

  More actions

  April 12, 2006 at 12:13 am

  #171168

  Hi Guys,

  One of the column in a table contain the list of values like 'hello, thanks, movies, tickets',

  I want substract word one by one.

  Please help me

  Thanks in adv.

   

   

  ---

  Kindest Regards,

  karthik
  

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  April 12, 2006 at 4:56 am

  #632049

  Hi Karthik,

  It's hard to understand what you're asking for, but maybe it's this...

  --data

  declare @t table (words varchar(100))

  insert @t

            select 'hello, thanks, movies, tickets'

  union all select 'abc, de, fghi'

  union all select 'xyz'

  --calculation

  while exists (select * from @t where words is not null)

  begin

      update @t set words = case when not charindex(',', words) = 0

          then left(words, len(words) - charindex(',', reverse(words))) end

      select * from @t

  end

  If not, please provide an example with the output(s) you are hoping to get.

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  April 12, 2006 at 8:11 am

  #632104

  SELECT

  LTRIM(SUBSTRING(',' + t.words + ',', n.number + 1,

  CHARINDEX(',', ',' + t.words + ',', n.number + 1) - n.number - 1))

  FROM [numbers] n

  INNER JOIN @t t

  ON SUBSTRING(',' + t.words + ',', n.number, 1) = ','

  AND n.number < LEN(',' + t.words + ',')

  Will need to create numbers table containing numbers from 1 to max len of column

  Far away is close at hand in the images of elsewhere.
  Anon.