Multi -column string search counting total no. of occurances

• Simon Goldsmith

  Grasshopper

  Points: 17

  More actions

  January 13, 2010 at 9:37 am

  #386092

  I am trying to alter a query I wrote over 2 years ago that does a keyword search on 2 joined database tables so that it has a count for the occurance of said keyword in each record returned.

  The current code I have only does the search i does not get the occurance total.

  SELECT DISTINCT GE.GENERIC_ID AS ID, GE.MENU_NAME, Cast(GE.CONTENT_1 AS nvarchar(4000)) AS CONTENT_1,

  GE.URL, GC.GENERIC_ID AS ID_2, GC.H1 AS H2, GC.PAGETYPE_ID

  FROM DC_GENERIC AS GE INNER JOINDC_GENERIC_PARENT_MENUTYPE AS GPM ON GE.GENERIC_ID = GPM.GENERIC_ID

  INNER JOIN DC_GENERIC_PARENT_MENUTYPE AS GCM ONGPM.GENERIC_ID = GCM.PARENT_ID

  INNER JOIN DC_GENERIC AS GC ON GCM.GENERIC_ID = GC.GENERIC_ID

  WHERE (GE.H1 LIKE '%' + @sSearch +'%'

  OR GE.CONTENT_1 LIKE '%' + @sSearch + '%'

  OR GE.CONTENT_2 LIKE '%" + @sSearch + "%'

  OR GE.CONTENT_3 LIKE '%' + @sSearch + "%'

  OR GE.CONTENT_4 LIKE '%' + @sSearch + "%'

  OR GE.CONTENT_5 LIKE '%' + @sSearch + '%'

  OR GC.H1 LIKE '%' + @sSearch + '%' AND GPM.MENUTYPE_ID = 7 AND GE.REMVALUE = 'N'

  ORDER BY GE.MENU_NAME, GC.H1

  Any help greatly appreciated

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  January 13, 2010 at 9:53 am

  #1103175

  Please show us sample output, I have a guess at that you want but I'm not sure.