Select Some Date From A long Text Column

• ahmedhussein787

  SSC Veteran

  Points: 221

  More actions

  May 13, 2012 at 12:18 pm

  #274065

  I have column has long Text Called Certificate Details which i want to select some data from it

  Table Structure (3Columns)

  ID, CertificateType, CertfiicateDetails

  1, Networks, [Name] Cristiano Ronaldo [Section A Grade] 70% [Section B Grade] 30% [TotalGrade] 50%

  2, Programming, [Name] Leo Messi [Section A Grade] 90% [Section B Grade] 50% [TotalGrade] 70%

  3, Soft Skills, [Name] Wayne Rooney [Section A Grade] 50% [Section B Grade] 20% [TotalGrade] 35%

  4, English [Name] Zinedine Zidane [Section A Grade] 35% [Section B Grade] 90% [TotalGrade] 65%

  5, Administration, [Name] Edgar Davids [Section A Grade] 80% [Section B Grade] 40% [TotalGrade] 60%

  i want to use select statement to retrieve like that

  Name, TotalGrade (2Columns)

  Cristiano Ronaldo, 50%

  Leo Messi, 70%

  Wayne Rooney, 35%

  Zinedine Zidane, 65%

  Edgar Davids, 60%

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  May 13, 2012 at 3:01 pm

  #1486891

  What you may want to do to make your life easier in the future is to normalize the data. Like this...

  --===== Create the test data.

  SELECT *

  INTO #TestTable

  FROM

  (

  SELECT 1, 'Networks', '[Name] Cristiano Ronaldo [Section A Grade] 70% [Section B Grade] 30% [TotalGrade] 50%' UNION ALL

  SELECT 2, 'Programming', '[Name] Leo Messi [Section A Grade] 90% [Section B Grade] 50% [TotalGrade] 70%' UNION ALL

  SELECT 3, 'Soft Skills', '[Name] Wayne Rooney [Section A Grade] 50% [Section B Grade] 20% [TotalGrade] 35%' UNION ALL

  SELECT 4, 'English', '[Name] Zinedine Zidane [Section A Grade] 35% [Section B Grade] 90% [TotalGrade] 65%' UNION ALL

  SELECT 5, 'Administration', '[Name] Edgar Davids [Section A Grade] 80% [Section B Grade] 40% [TotalGrade] 60%'

  ) d (ID, CertificateType, CertificateDetails)

  ;

  --===== Normalize all the data

  WITH

  cteSplit AS

  (

  SELECT tt.ID, tt.CertificateType,

  DetailName = SUBSTRING(ca.Item, 1, CHARINDEX(']',ca.Item)-1),

  DetailValue = SUBSTRING(ca.Item, CHARINDEX(']',ca.Item)+ 2, LEN(ca.Item))

  FROM #TestTable tt

  CROSS APPLY dbo.DelimitedSplit8K(CertificateDetails,'[') ca

  WHERE ca.ItemNumber > 1

  )

  SELECT ID, CertificateType,

  Name = MAX(CASE WHEN DetailName = 'Name' THEN DetailValue ELSE '' END),

  [Section A Grade] = MAX(CASE WHEN DetailName = 'Section A Grade' THEN DetailValue ELSE '' END),

  [Section B Grade] = MAX(CASE WHEN DetailName = 'Section B Grade' THEN DetailValue ELSE '' END),

  TotalGrade = MAX(CASE WHEN DetailName = 'TotalGrade' THEN DetailValue ELSE '' END)

  FROM cteSplit

  GROUP BY ID, CertificateType

  ;

  Results:

  ID CertificateType Name Section A Grade Section B Grade TotalGrade

  -- --------------- ----------------- --------------- --------------- ----------

  5 Administration Edgar Davids 80% 40% 60%

  4 English Zinedine Zidane 35% 90% 65%

  1 Networks Cristiano Ronaldo 70% 30% 50%

  2 Programming Leo Messi 90% 50% 70%

  3 Soft Skills Wayne Rooney 50% 20% 35%

  You can get the "DelimitedSplit8K" function from the "Resources" section near the end of the following article.

  http://qa.sqlservercentral.com/articles/Tally+Table/72993/

  '

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions