Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Question Unanswered: Listing words with no of occurances in sql server

    Hi,

    Is it possible to get words from text columns in a sql server database in the order of their occurances in that column with full text search or by any other method.


    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this approach -- Simple Keyword Relevance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    Posts
    5

    Cool My problem is some thing different



    My query is regarding retreiving words from table on the bases of its maximum occurances in column irrespective of a row.

    means a word that appears maximum number of times in a column, suppose if i have 10 rows in a table and word "Globe" appears maximum no of times say in 5 rows i want Globe word on the top.


    Thanks in advance.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select wordcolumn
         , count(*) as occurrences
      from daTable
    group
        by wordcolumn
    order
        by occurrences desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    5
    Hi,
    Thanks for quick reply....

    This could solve my problem if reffered column contain only single word at a time.
    But this column is varchar column containing text of multiple words.

    Any suggestion?

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ok, that's going to require that you split out every word in the column individually

    this may require a cross join with a numbers table

    what's this for? how often are you going to use it?

    the query will definitely not be easy to write, nor efficient to run
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Please read the sticky at the top of the board and post what it asks for...we need some examples
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you gonna write the query, brett?

    here's an example --
    Code:
    create table somewords
    ( id integer not null primary key identity
    , blah text not null
    );
    insert into somewords (blah) 
    values ('a word that appears maximum number of times in a column')
    insert into somewords (blah) 
    values ('Is it possible to get words from text columns in a sql server database')
    insert into somewords (blah) 
    values ('This could solve my problem if reffered column contain only single word')
    insert into somewords (blah) 
    values ('that''s going to require that you split out every word in the column individually')
    insert into somewords (blah) 
    values ('the query will definitely not be easy to write')
    insert into somewords (blah) 
    values ('Please read the sticky at the top of the board')
    insert into somewords (blah) 
    values ('The physical order of data in a database has no meaning')
    i'm sure you understand the requirement -- which word occurs the most frequent?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2006
    Posts
    5
    Hi,

    r937 quoted the right example and i am sure he understood the problem.

    I need it often in my application.

    If you say to split words in multiple columns, the problem here is we are not sure about maximum number of words in field.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is this for doing a search and ranking the results based on multiple search criteria...if it is then Rudy did not understan...because what you are asking makes no sense

    Is it really show me for all rows in a table, the rows with the most occuring types?

    If it is, and you need to "do that alot", what in heavens name for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and why would you need a query

    http://www.google.com/search?hl=en&l...=a&btnG=Search

    About 8 billion hits

    I think that would "win"
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the question is really very simple, brett

    refer to the sample data which i posted (which you wanted to see), and answer the question: which word occurs most frequently in the blah column? hint: it isn't the word "data" and it isn't the word "word"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, well, you would have to start be decontructig the data...but I still don't know what for or why there would be a need for this

    Code:
    
    create table somewords
    ( id integer not null primary key identity
    , blah text not null
    );
    insert into somewords (blah) 
    values ('a word that appears maximum number of times in a column')
    insert into somewords (blah) 
    values ('Is it possible to get words from text columns in a sql server database')
    insert into somewords (blah) 
    values ('This could solve my problem if reffered column contain only single word')
    insert into somewords (blah) 
    values ('that''s going to require that you split out every word in the column individually')
    insert into somewords (blah) 
    values ('the query will definitely not be easy to write')
    insert into somewords (blah) 
    values ('Please read the sticky at the top of the board')
    insert into somewords (blah) 
    values ('The physical order of data in a database has no meaning')
    GO
    
    CREATE TABLE UniqueWords (
    	  Word varchar(256)
    	, WordId int IDENTITY(1,1)
    	, Add_Dt datetime DEFAULT (GetDate()))
     GO
    
    CREATE UNIQUE INDEX UnqueWords_PK ON UniqueWords(Word)
    GO
    
    CREATE PROC isp_INS_UNIQUE_WORDS
    AS
    BEGIN
    	SET NOCOUNT ON
    	DECLARE @Words INT, @Pos INT, @x Int, @str varchar(256)
    		  , @word varchar(256), @start int, @end int, @exitstart int
    	SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
    
    	DECLARE myCursor CURSOR FOR SELECT Blah FROM SomeWords
    	OPEN myCursor
    	FETCH NEXT FROM myCursor INTO @str
    
    	WHILE @@FETCH_STATUS = 0
    	  BEGIN
    		WHILE (@x <> 0)
    			BEGIN
    				SET @x     = CHARINDEX(' ', @str, @Pos)
    				IF @x <> 0
    				  BEGIN 
    					SET @end   = @x - @start
    					SET @word  = SUBSTRING(@str,@start,@end)
    					IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
    						INSERT INTO UniqueWords(Word) SELECT @word
    					-- SELECT @Word, @@ROWCOUNT,@@ERROR
    					-- SELECT @x, @Word, @start, @end, @str
    					SET @exitstart = @start + @end + 1
    					SET @Pos   = @x + 1
    					SET @start = @x + 1
    					SET @Words = @Words + 1
    				  END
    				IF @x = 0
    				  BEGIN
    					SET @word  = SUBSTRING(@str,@exitstart,LEN(@str)-@exitstart+1)
    					IF NOT EXISTS (SELECT * FROM UniqueWords WHERE Word = @Word)
    						INSERT INTO UniqueWords(Word) SELECT @word
    					-- SELECT @Word, @@ROWCOUNT,@@ERROR
    					-- SELECT @x, @Word, @exitstart, LEN(@str)-@exitstart, @str
    				  END
    			END
    		FETCH NEXT FROM myCursor INTO @str
    		SELECT @Words = 0, @Pos = 1, @x = -1, @Word = '', @start = 1
    	  END	
    
    	  CLOSE myCursor
    	  DEALLOCATE myCursor
    	  SET NOCOUNT OFF
    	RETURN @Words
    END
    GO
    
    EXEC isp_INS_UNIQUE_WORDS
    GO
    
    SELECT * FROM UniqueWords ORDER BY Word
    GO
    
    DROP PROC isp_INS_UNIQUE_WORDS
    DROP TABLE UniqueWords, somewords
    GO
    I'm gonna blog this
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    ...but I still don't know what for or why there would be a need for this
    You don't think there's a use for data mining on simple text?

    let's see....

    military intel
    or
    political speech analysis
    or
    most occurences in scientific notation
    ...
    Inspiration Through Fermentation

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brett! dude!! that's wonderful! and the cursors, so pretty!!

    now, any chance you could actually address the problem as initially posted?

    which word occurred the most often?

    boy it's taking you a long time to catch on

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •