need to Display one word from string

• surma.sql

  SSCommitted

  Points: 1552

  More actions

  July 9, 2012 at 8:42 am

  #272175

  Hello

  I have trouble to Display one word from each row.

  the string is

  PSSA Math

  PSSA Science

  PSSA Reading

  PSSA Writing

  PSSA-M Math

  PSSA-M Science

  PSSA-M Reading

  PSSA-M Writing

  so i just only need to display subjects.

  as

  Math

  Science

  Reading

  Writing

  so how it possible.

  is it possible with CASE When Statement

  Please help me through this

  Thanks

• davidandrews13

  SSCertifiable

  Points: 6809

  More actions

  July 9, 2012 at 9:08 am

  #1510390

  declare @table table(id varchar(50))

  insert into @table

  select 'PSSA Math'

  union all

  select 'PSSA Science'

  union all

  select 'PSSA Reading'

  union all

  select 'PSSA Writing'

  union all

  select 'PSSA-M Math'

  union all

  select 'PSSA-M Science'

  union all

  select 'PSSA-M Reading'

  union all

  select 'PSSA-M Writing'

  select

  SUBSTRING(id, charindex(' ',id,1) +1 ,len(id) )

  from @table

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 9, 2012 at 9:10 am

  #1510391

  Why do you need a case statement ? When something like this will produce the results you have indicated that you require.

  CREATE TABLE #T(theStringis VARCHAR(50))

  INSERT INTO #T

  SELECT 'PSSA Math' UNION ALL

  SELECT 'PSSA Science' UNION ALL

  SELECT 'PSSA Reading' UNION ALL

  SELECT 'PSSA Writing' UNION ALL

  SELECT 'PSSA-M Math' UNION ALL

  SELECT 'PSSA-M Science' UNION ALL

  SELECT 'PSSA-M Reading' UNION ALL

  SELECT 'PSSA-M Writing'

  SELECT SUBSTRING(thestringis,CHARINDEX(' ',thestringis,1)+1,DATALENGTH(thestringis)) FROM #T

  Result:

  Math

  Science

  Reading

  Writing

  Math

  Science

  Reading

  Writing

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• surma.sql

  SSCommitted

  Points: 1552

  More actions

  July 9, 2012 at 9:42 am

  #1510410

  Thank You So much David and Ron

  Appreciate

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 9, 2012 at 9:45 am

  #1510415

  And may I thank you for letting me know that I may have been of some benefit... too often those who are helped do not acknowledge the help given.... so again let me say THANK YOU

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]