MySQL, rank keywords separated by space

I've a table with only one col, and contain free text, and each keyword separated by a space

TEXT
apple orange dog
apple banna cat

I want to have a result sth like

apple 2
orange 1
dog 1
banna 1
cat 1

Is it possible with just SQL?

Answers


Try this

create Function

Create FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Query

 declare @ColumnNameList VARCHAR(MAX)
select @ColumnNameList  = COALESCE(@ColumnNameList +' ' ,'') +column  from table
select @ColumnNameList 
select value,count(value) as count from (select value from fn_split((select @ColumnNameList),' '))
Space_tbl group by value

Need Your Help

WCF App using Peer Chat app as example does not work

wcf sample p2p

I converted a VB .Net 3.5 app to use peer to peer WCF using the available Microsoft example of the Chat app. I made sure that I copied the app.config file for the sample(modified the names for my ...

Performance of vector::size() : is it as fast as reading a variable?

c++ gcc stl vector

I have do an extensive calculation on a big vector of integers. The vector size is not changed during the calculation. The size of the vector is frequently accessed by the code. What is faster in g...