1

How can I seek those rows which contain some text and get a mark depending on how many times this text was repeated?

  • I have SQL Server CTP3 installed but I don't know where to start
  • Maybe I can use like %criteria% and then count how many times the text is repeated on each record but it doesn't seem very efficient.
  • It would be great if there was any routine on SQL Server for doing it
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jaime Oro
  • 9,899
  • 8
  • 31
  • 39
  • Can you do this in two passes? – alexn Sep 15 '11 at 06:21
  • 1
    What do you mean by mark? Can you not just use count() with a where clause? – TheCodeKing Sep 15 '11 at 06:23
  • 1
    Check out [SQL Server Fulltext Search](http://msdn.microsoft.com/en-us/library/ms142571.aspx) which allows just that... It's the [CONTAINSTABLE function](http://msdn.microsoft.com/en-us/library/ms189760.aspx) – marc_s Sep 15 '11 at 06:30

2 Answers2

1

If I understand what you are trying to do, I suggest you use a text to word list function. There's a few examples here. If using full-text indexing, you could even use CONTAINSTABLE. You can then join to this table value function and aggregate the count. 

 Select t.*, 
    (Select count(w.*) 
     From dbo.getWordList(t.text) w
     Where w.word like '%criteria%') as Count
 From MyTable t
 Where t.text like '%criteria%'

Alternatively you could just solve the entire problem in a CLR function.

Community
  • 1
  • 1
TheCodeKing
  • 19,064
  • 3
  • 47
  • 70
0

If you wanna get the appearance times of string in a single SQL Server data record, the RegEx would be more appropriate.

Ming Yeh
  • 1
  • 1
  • 1