Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
oasis1
Starting Member
35 Posts |
 Posted - 2012-06-10 : 14:29:00
|
| 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 belowID Line Note123 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
52326 Posts |
Posted - 2012-06-10 : 14:38:27
|
| [code]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[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2012-06-10 : 23:32:24
|
| 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 Helperhttp://www.sql-server-helper.com/error-messages/msg-1-500.aspx |
|
|
|
|
|
|
Find string in multiple records