How to count the number of times a string appears in a database field.

February 21, 2008 10:30 AM

SQL Filter: What query can I use to count the number of times a word appears in a text field?

I've got this table with ~500 records. The records have a specific text field that I'm interested in. Before I start a fairly involved find-and-replace project, I want to know:

a. how many records will need to have the find-and-replace function performed (I think I've got this one, using the 'LIKE' operator)

and

b. for a given record, how many instances of the find-and-replace operation will need to happen?


For example, if I were changing the word 'cat' to the word 'dog', I'd want to know how many times the word 'cat' appeared in a given field for each record.

I know I could query the db and parse the results with PHP, but I was looking for a solution I could run from the mysql 'command line'.

posted by Wild_Eep to computers & internet (6 comments total)

What database?
posted by d4nj450n at 10:40 AM on February 21, 2008

I would find the length of the original field, then subtract the length of the field after you've replaced the word, then divide by the length of the word you replaced.

Something like this, adjust for your db language appropriately:

(len(textField) - len(replace(textField, 'cat', '')))/len('cat') as numOccurrences

You need to take into account words that contain 'cat' within them though, like "concatenate" or "catch" for example.
posted by gatorbiddy at 10:51 AM on February 21, 2008 [1 favorite has favorites]

I'm using mySQL.
posted by Wild_Eep at 10:54 AM on February 21, 2008

a. SELECT COUNT(myColumn) AS count FROM myTable WHERE myColumn LIKE '%cat%'

b. are you saying you might have a record with 'cat cat cat' in it, and you want it to return 3 replaces necessary?
posted by cschneid at 11:29 AM on February 21, 2008

gatorbiddy nailed it. Rack 'em.
posted by neilkod at 12:47 PM on February 21, 2008

Since the data is already in a database, there should be no cut-and-paste. Just do something like:

UPDATE mytable SET textfield = replace(textfield, 'cat', 'dog')

or run the values through a simple regular expression if you need more control (like retaining capitalization, making sure you don't capture substrings, etc).
posted by Horselover Fat at 6:41 PM on February 21, 2008