SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find string in multiple records		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oasis1
Starting Member

28 Posts
Posted - 06/10/2012 :  14:29:00  Show Profile  Reply with Quote
We have a system that stores notes in 250 character pieces I need to search for a specific string like '%Counsel%Smoking%Cessation%medication%'. I want to make sure there is no break where the string is split on to seperate lines ie. 1 then continues on 2. So I want to create one big string out of the note then search. I have a few ideas with a CTE but was wondering if there is a way to get the number of occurences of LINE column since it can be different for each ID, Data example below

ID Line Note
123 1 something...
123 2 something...
123 3 something...
456 1 something...
456 2 something...
456 3 something...
456 4 something...
456 5 something...

Mahalo for your help...

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 06/10/2012 :  14:38:27  Show Profile  Reply with Quote 

SELECT ID,
STUFF((SELECT ' ' + Note FROM Table WHERE ID = t.ID ORDER BY Line FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT ID FROM table)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sshelper
Posting Yak Master

213 Posts
Posted - 06/10/2012 :  23:32:24  Show Profile  Visit sshelper's Homepage  Reply with Quote
If I understood your question correctly, here's how to get the number of occurrences of LINE column given you sample data:

SELECT [ID], MAX([Line]) AS [MaxLine], COUNT(*) AS [LineCount]
FROM [dbo].[YOurTable]
GROUP BY [ID]

Hope this helps.
SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000