Mysql – Order result by number of occurence of a word in a text

MySQLorder-byselectsqlite

I am working with sqlite3/python .

I would like to order results by the number of occurence of %@% in text.

For exemple

Jimmy 10 // Jimmy wrote 10 strings like %@%
James 2
Sophia 0
..etc

The code could be something like this :

cursor = db.execute("SELECT name, text 
                       FROM MyTable
                      WHERE text LIKE '%@%'
                      ORDER BY someMagic DESC")

I am looking for someMagic if it exists 🙂

Best Answer

SELECT LENGTH(text)-LENGTH(REPLACE(text,'@',''))as counting from table
ORDER BY counting DESC

SQL Fiddle

Related Solutions

Sql-server – SQL Server 2005: Full-text search, order by matches

You could try FREETEXTTABLE:

SELECT ft.[Rank], p.*
FROM dbo.products AS p 
INNER JOIN FREETEXTTABLE(products, productname, 'tennis ball') AS ft  
ON ft.key_column = p.key_column  
ORDER BY ft.[Rank] DESC;

Mysql – Best way to set up database tables

You have no need to add a "modules" field to your main/users table. Just JOIN on the tables -- that's what Relational Databases are for. INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions. If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

SELECT * 
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins

Best Answer

Related Solutions

Sql-server – SQL Server 2005: Full-text search, order by matches

Mysql – Best way to set up database tables

Related Question